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

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

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

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

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

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

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

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

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

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

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

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

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