How to extract text from middle of a string
 

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

How to extract text from middle of a string
 

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

Makelei wrote:
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

How to extract text from middle of a string
 

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" wrote:

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

How to extract text from middle of a string
 

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" wrote:

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" wrote:

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

How to extract text from middle of a string
 

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" wrote:

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" wrote:

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" wrote:

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

How to extract text from middle of a string
 

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

"OssieMac" wrote:

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" wrote:

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" wrote:

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" wrote:

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

How to extract text from middle of a string
 

=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)

"Makelei" wrote in message
...
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

"OssieMac" wrote:

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" wrote:

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" wrote:

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" wrote:

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

How to extract text from middle of a string
 

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

"Makelei" wrote in message
...
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?

"OssieMac" wrote:

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.

"Makelei" wrote:

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" wrote:

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" wrote:

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

How to extract text from middle of a string
 

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

BR
Markku

"Bob Phillips" wrote:

=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)

"Makelei" wrote in message
...
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

"OssieMac" wrote:

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" wrote:

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" wrote:

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" wrote:

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

How to extract text from middle of a string
 

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

"Makelei" wrote:

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

How to extract text from middle of a string
 

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)

On May 22, 12:21 pm, ACarella
wrote:
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

"Makelei" wrote:
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

How to extract text from middle of a string
 

Perfect. It worked.
Thank you.
Arlene

"Reitanos" wrote:

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)

On May 22, 12:21 pm, ACarella
wrote:
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

"Makelei" wrote:
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

How to extract text from middle of a string
 

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.

"ACarella" wrote:

Perfect. It worked.
Thank you.
Arlene

"Reitanos" wrote:

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)

On May 22, 12:21 pm, ACarella
wrote:
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

"Makelei" wrote:
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

How to extract text from middle of a string
 

If you have commas, of course you can

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"keithobro" wrote in message
...
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.

"ACarella" wrote:

Perfect. It worked.
Thank you.
Arlene

"Reitanos" wrote:

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)

On May 22, 12:21 pm, ACarella
wrote:
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

"Makelei" wrote:
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

How to extract text from middle of a string
 

On Fri, 27 Jun 2008 13:28:00 -0700, keithobro
wrote:

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

How to extract text from middle of a string
 

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" wrote:

On Fri, 27 Jun 2008 13:28:00 -0700, keithobro
wrote:

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

How to extract text from middle of a string
 

On Sat, 28 Jun 2008 09:54:01 -0700, keithobro
wrote:

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

How to extract text from middle of a string
 

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" wrote:

On Sat, 28 Jun 2008 09:54:01 -0700, keithobro
wrote:

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

How to extract text from middle of a string
 

On Wed, 9 Jul 2008 06:25:00 -0700, bollard
wrote:

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