How to extract text from middle of a string

[Makelei]

Hi,
I have in cell text as follows:
Text1 - Text2 - Text3

I need to get out Text2 in one cell and Text3 to another. What kinf of
formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3
characters.

Thanks in advance
MakeLei

 

[David Hilberg]

If the full text is in A1:

Text2:
=MID(A1, 7, FIND("-", A1, 7) -8)


Text3:
=RIGHT(A1, LEN(A1) -9 -LEN(B1))
This assumes that Text2 is in cell B1.

Both formulas assume that:
- No dashes exist within Text1.
- Text1 is always three characters, and the texts are separated by " - ".


David

 

[OssieMac]

Hi MakeLei,

Need some confirmation. Is there always a space, hyphen and space between
each text string as you have written it.

Is it a column of data that you are trying to divide into separate
components? If it is then you can use Text to Columns Wizard and use a space
as a delimiter (or if the hyphens exist then the hyphens as the delimiters).
Lookup Text to columns wizard in Help.

If the above does not help then post a sample of the original data and a
sample of how you want it to appear.

Regards,

OssieMac

 

[Makelei]

Hi,
The format of the column is as example:
"Ext- text2 that might have spaces - Text3 that have spaces"

I need to be able to get staright text: "Text2 that might have spaces"
and "Text3 that have spaces"

BR
MakeLei

 

[OssieMac]

OK Makelei,

You should be able to do that with Text to Columns wizard. Look it up in
help and see how you go. Use the hyphen (-) as the delimiter.

Regards,

OssieMac

 

[Makelei]

Hi,
Thanks for you answer, but it does not help me (I know how to use that),
since I need to be able to use that data in other formulas...

Any proposals?

BR
MakeLei

 

[Bob Phillips]

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

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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[David Biddulph]

How does that stop you using the data in other formulae?

 

[Makelei]

Hi All,
Thanks for Bob Phillips for the formulae. These are the ones I was looking
for.

BR
Markku

 

[ACarella]

Hi Makelei:

I have in cell C2 the following
H01.12345678

I need to extract the 2nd and 3rd character from the left of the decimal into
cell G2

Can you help?
thank you Arlene

 

[Reitanos]

If what you mean is get the 2 characters to the left of the decimal,
but there might be more than 3 characters on the left you do this:
=MID(C4,FIND(".",C4)-2,2)
Basically, it locates the decimal and then uses its position to get
the other text.
If there will always be the same number of characters you can skip the
FIND part:
=MID(C4,2,2)

 

[ACarella]

Perfect. It worked.
Thank you.
Arlene

If what you mean is get the 2 characters to the left of the decimal,
but there might be more than 3 characters on the left you do this:
=MID(C4,FIND(".",C4)-2,2)
Basically, it locates the decimal and then uses its position to get
the other text.
If there will always be the same number of characters you can skip the
FIND part:
=MID(C4,2,2)

 

[keithobro]

I have a similar problem. I have a name in cell A2. It will consist of Title,
Initials, Surname, e.g:

Mr A Bloggs
Mrs D P Smithers
Miss L Young

I need to separate the Title, Initials and Surname into 3 columns. Can you
suggest how this can be achieved, please? I clearly can't use Text to Columns
because the components are of variable length.

Thanks.

 

[Don Guillett]

If you have commas, of course you can

 

[Ron Rosenfeld]

I have a similar problem. I have a name in cell A2. It will consist of Title,
Initials, Surname, e.g:

Mr A Bloggs
Mrs D P Smithers
Miss L Young

I need to separate the Title, Initials and Surname into 3 columns. Can you
suggest how this can be achieved, please? I clearly can't use Text to Columns
because the components are of variable length.

Assumptions:

1. Every entry has a title.
2. Every last name is only a single word.

A2: Original Name

Title:
B2: =LEFT(A2,FIND(" ",A2)-1)

Initials:
C2: =TRIM(MID(A2,LEN(B2)+1,FIND(D2,A2)-FIND(" ",A2)-1))

Last Name:
D2: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

--ron

 

[keithobro]

Hi Ron

For a moment there, I thought the middle formula wasn't going to work, then,
once I'd input the 3rd one, it all fell into place.

Marvellous.

Can't thank you enough.

Keith

 

[Ron Rosenfeld]

Hi Ron

For a moment there, I thought the middle formula wasn't going to work, then,
once I'd input the 3rd one, it all fell into place.

Marvellous.

Can't thank you enough.

Keith

You're welcome. Glad to help. Thanks for the feedback.

Of course, it is possible to make the "middle formula" stand alone by
substituting the formula that is in D2 for the D2 in the middle formula, but I
chose not to.

A stand-alone formula for the "middle":

=MID(A2,FIND(" ",A2)+1,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),
LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2)-1)
--ron

 

[bollard]

Hi Ron

We've put the formulae you gave into place and have since identfied one
problem. It's OK at identifying all the initials that occur between title and
surname, but it can't cope with people whose surname consists of 2 words or
more, that are not hyphenated, e.g.:

van Bommel
le Clerc
van den Bosch
de la Rue

Is there any way we can tweak the formula for the initials to recognise
strings as opposed to indiviudal initials? When it finds, for example, van
Bommel, it treats that surname as if 1 word.

Hope that all makes sense!

Thanks.

Keith

 

[Ron Rosenfeld]

Hi Ron

We've put the formulae you gave into place and have since identfied one
problem. It's OK at identifying all the initials that occur between title and
surname, but it can't cope with people whose surname consists of 2 words or
more, that are not hyphenated, e.g.:

van Bommel
le Clerc
van den Bosch
de la Rue

Is there any way we can tweak the formula for the initials to recognise
strings as opposed to indiviudal initials? When it finds, for example, van
Bommel, it treats that surname as if 1 word.

Hope that all makes sense!

Thanks.

Keith

With this level of complexity, I would use VBA functions. But we would need to
know the entire range of possible data in order to do this.

For example, if it is the case that the format is always:

<title> <0-n initials> <1-n strings of at least 2 letters>

this can be easily done. Let me know if this is the case, or if there is more
variation.
--ron