General discussion

Locked

Excel - formula help to extract a string

By mrmolio ·
I'm looking for the formula to help me pull string values out of a cell, based on certain characters. For example:

If A1 has the string "Mr. Jones - Managing director"

I'd like to pull out "Mr. Jones" and "Managing Director" into separate cells. I know it's some combination of text functions, but can't seem to get the right mix. Can you help?

thanks,
Craig

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel - formula help to extract a string

by Don Christner In reply to Excel - formula help to e ...

Select the area that you wish to work with, then select DATA, then TEXT TO COLUMNS. This will allow you to select what delimiter to use when you split your cells.

You will basically split between words if you use "space" as your delimiter. Maybe you will want to doctor your data a little before you do the split.

Don

Collapse -

Excel - formula help to extract a string

by mrmolio In reply to Excel - formula help to e ...

Poster rated this answer

Collapse -

Excel - formula help to extract a string

by Bill_H In reply to Excel - formula help to e ...

Given that A1 has the string "Mr. Jones - Managing director"...

Place the following formula in a cell to extract the value "Mr. Jones":

=MID(A1,1,FIND(" - ",A1)-1)

Place the following formula in a cell to extract the value "Managing director":

=MID(A1,FIND(" - ",A1)+3,LEN(A1)-(FIND(" - ",A1)+1))

This is based on certain characters, in this case the string " - ".

The formulae are a bit clunky, and particularly the second one, since it uses the FIND function twice. This couldbe simplified as follows:

Use a cell to hold the position of the search string (" - "). For example, place the following formula into A3:

=FIND(" - ",A1)

Then substitute the value of A3 for the FIND function in the other formulae:

=MID(A1,1,A3-1)

-and-

=MID(A1,A3+3,LEN(A1)-(A3+1))

If you do not want the contents of cell A3 to be visible, there are several methods by which it can be hidden.

Hope this helps...

Collapse -

Excel - formula help to extract a string

by mrmolio In reply to Excel - formula help to e ...

Poster rated this answer

Collapse -

Excel - formula help to extract a string

by Peyison In reply to Excel - formula help to e ...

Bill's answer above is correct - but you can simplify the second formula:

=MID(A1,FIND("-",A1)+1,LEN(A1))

The difference is the third parameter of the MID() function. It is the number of characters to return, but if you want to include all theremaining characters in a string, you can enter a large number. Just make sure the number is larger than the max length of that part of the original string. If you use LEN() of the original string, you can ensure that the number will be large enough.

Another option to consider is adding the TRIM() function:

=TRIM(MID(A1,FIND("-",A1)+1,LEN(A1)))

This will remove the leading space, if there is one.

Hope this helps.

Collapse -

Excel - formula help to extract a string

by mrmolio In reply to Excel - formula help to e ...

Poster rated this answer

Collapse -

Excel - formula help to extract a string

by mrmolio In reply to Excel - formula help to e ...

Thanks for your help!

Collapse -

Excel - formula help to extract a string

by mrmolio In reply to Excel - formula help to e ...

Poster rated this answer

Collapse -

Excel - formula help to extract a string

by mrmolio In reply to Excel - formula help to e ...

This question was closed by the author

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums