Extracting text from a string
 

I have a list of model descriptions in a column such as:

Accord 2.0i VTEC SE Auto HFT,VSA,17''tec
Civic 5Dr 1.8 ES Manual 17" Epsilon A

I need code to extract the first word, in this case a car model name, and
place it in the next column on its own. There is always a space after the
required word if this helps.

Also I need to extract the numbers describing the engine size ( 2.0 , 1.8
etc) and place them in column C. These could be in any position in the text
string in column A

I.e.

Column A Column B
Column C
Accord 2.0i VTEC SE Auto HFT,VSA,17''tec Accord 2.0
Civic 5Dr 1.8 ES Manual 17" Epsilon A Civic
1.8


Can you help please

Extracting text from a string
 

This can be done with functions either on string or through code, the first
should work as long is it is not 2 names, and the second will pull the size
if it has the . 2 digits before and 1 after, this can be modified for 2 on
either side.
=LEFT(A1,FIND(" ",A1)-1)
=TRIM(MID(A1,FIND(".",A1)-2,4))
this assumes data in A1 change the 4 to a 5 to get 2 digits on either side
of the .
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Alan M" wrote:

I have a list of model descriptions in a column such as:

Accord 2.0i VTEC SE Auto HFT,VSA,17''tec
Civic 5Dr 1.8 ES Manual 17" Epsilon A

I need code to extract the first word, in this case a car model name, and
place it in the next column on its own. There is always a space after the
required word if this helps.

Also I need to extract the numbers describing the engine size ( 2.0 , 1.8
etc) and place them in column C. These could be in any position in the text
string in column A

I.e.

Column A Column B
Column C
Accord 2.0i VTEC SE Auto HFT,VSA,17''tec Accord 2.0
Civic 5Dr 1.8 ES Manual 17" Epsilon A Civic
1.8


Can you help please

Extracting text from a string
 

Try,

=IF(ISERR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1))

or if not worried about errors the simpler

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

Mike

"Alan M" wrote:

I have a list of model descriptions in a column such as:

Accord 2.0i VTEC SE Auto HFT,VSA,17''tec
Civic 5Dr 1.8 ES Manual 17" Epsilon A

I need code to extract the first word, in this case a car model name, and
place it in the next column on its own. There is always a space after the
required word if this helps.

Also I need to extract the numbers describing the engine size ( 2.0 , 1.8
etc) and place them in column C. These could be in any position in the text
string in column A

I.e.

Column A Column B
Column C
Accord 2.0i VTEC SE Auto HFT,VSA,17''tec Accord 2.0
Civic 5Dr 1.8 ES Manual 17" Epsilon A Civic
1.8


Can you help please

Extracting text from a string
 

Missed the engine size:-

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

This works for both the examples given but is far from bullet proof.

Mike

"Alan M" wrote:

I have a list of model descriptions in a column such as:

Accord 2.0i VTEC SE Auto HFT,VSA,17''tec
Civic 5Dr 1.8 ES Manual 17" Epsilon A

I need code to extract the first word, in this case a car model name, and
place it in the next column on its own. There is always a space after the
required word if this helps.

Also I need to extract the numbers describing the engine size ( 2.0 , 1.8
etc) and place them in column C. These could be in any position in the text
string in column A

I.e.

Column A Column B
Column C
Accord 2.0i VTEC SE Auto HFT,VSA,17''tec Accord 2.0
Civic 5Dr 1.8 ES Manual 17" Epsilon A Civic
1.8


Can you help please

Extracting text from a string
 

Hello Mike

Thanks, that does work for an individual cell but when I try to copy the
formula down the column it returns the same answer in all cells rather than
the appropriate answer for that row

"Alan M" wrote:

I have a list of model descriptions in a column such as:

Accord 2.0i VTEC SE Auto HFT,VSA,17''tec
Civic 5Dr 1.8 ES Manual 17" Epsilon A

I need code to extract the first word, in this case a car model name, and
place it in the next column on its own. There is always a space after the
required word if this helps.

Also I need to extract the numbers describing the engine size ( 2.0 , 1.8
etc) and place them in column C. These could be in any position in the text
string in column A

I.e.

Column A Column B
Column C
Accord 2.0i VTEC SE Auto HFT,VSA,17''tec Accord 2.0
Civic 5Dr 1.8 ES Manual 17" Epsilon A Civic
1.8


Can you help please

Extracting text from a string
 

Alan,

I had assumed your data are in Column A down but from what you describe
that's not the case your data must be in a row so in dragging it down the
formula is querying itself and so returning the same answer which should tech
me to read more carefully.

The answers the same, put the formula in A2 to look at A1 and drag right.


Mike

"Alan M" wrote:

Hello Mike

Thanks, that does work for an individual cell but when I try to copy the
formula down the column it returns the same answer in all cells rather than
the appropriate answer for that row

"Alan M" wrote:

I have a list of model descriptions in a column such as:

Accord 2.0i VTEC SE Auto HFT,VSA,17''tec
Civic 5Dr 1.8 ES Manual 17" Epsilon A

I need code to extract the first word, in this case a car model name, and
place it in the next column on its own. There is always a space after the
required word if this helps.

Also I need to extract the numbers describing the engine size ( 2.0 , 1.8
etc) and place them in column C. These could be in any position in the text
string in column A

I.e.

Column A Column B
Column C
Accord 2.0i VTEC SE Auto HFT,VSA,17''tec Accord 2.0
Civic 5Dr 1.8 ES Manual 17" Epsilon A Civic
1.8


Can you help please