Home |
Search |
Today's Posts |
#1
|
|||
|
|||
EXTRACT TEXT FROM TEXT STRING
Hi Folks -
Using Excel 2003, I'm looking for a formula, ultimately to mailmerge from Word 2003, to extract a name from cells that contain a name and a number. The names are of variable length as are the numbers. In all cases there is a space between the name and the number. Examples a Anthony 2 Ber 12 Philomena 123. I've looked at LEFT and RIGHT and TRIM but can't see any way to go left from the space or right to the space. Many thanks. ANTHONY |
#2
|
|||
|
|||
Try this:
=MID(A1,SEARCH(" ",A1,1)+1,LEN(A1)-SEARCH(" ",A1,1)) You could also parse the data using DATA - TEXT TO COLUMNS "carricka" wrote: Hi Folks - Using Excel 2003, I'm looking for a formula, ultimately to mailmerge from Word 2003, to extract a name from cells that contain a name and a number. The names are of variable length as are the numbers. In all cases there is a space between the name and the number. Examples a Anthony 2 Ber 12 Philomena 123. I've looked at LEFT and RIGHT and TRIM but can't see any way to go left from the space or right to the space. Many thanks. ANTHONY |
#3
|
|||
|
|||
Try
=MID(A5,1,SEARCH(" ",A5)) Where text is in A5 Geoff "carricka" wrote in message ... Hi Folks - Using Excel 2003, I'm looking for a formula, ultimately to mailmerge from Word 2003, to extract a name from cells that contain a name and a number. The names are of variable length as are the numbers. In all cases there is a space between the name and the number. Examples a Anthony 2 Ber 12 Philomena 123. I've looked at LEFT and RIGHT and TRIM but can't see any way to go left from the space or right to the space. Many thanks. ANTHONY |
#4
|
|||
|
|||
Hi Barb,
This gives me the number instead of the name. It's not what I want but is most useful anyway. Geoff has supplied a formula that gives me the name. Thanks very much for your time. ANTHONY "Barb R." wrote: Try this: =MID(A1,SEARCH(" ",A1,1)+1,LEN(A1)-SEARCH(" ",A1,1)) You could also parse the data using DATA - TEXT TO COLUMNS "carricka" wrote: Hi Folks - Using Excel 2003, I'm looking for a formula, ultimately to mailmerge from Word 2003, to extract a name from cells that contain a name and a number. The names are of variable length as are the numbers. In all cases there is a space between the name and the number. Examples a Anthony 2 Ber 12 Philomena 123. I've looked at LEFT and RIGHT and TRIM but can't see any way to go left from the space or right to the space. Many thanks. ANTHONY |
#5
|
|||
|
|||
Hi Geoff -
Works perfectly, thanks. ANTHONY "Geoff Lord" wrote: Try =MID(A5,1,SEARCH(" ",A5)) Where text is in A5 Geoff "carricka" wrote in message ... Hi Folks - Using Excel 2003, I'm looking for a formula, ultimately to mailmerge from Word 2003, to extract a name from cells that contain a name and a number. The names are of variable length as are the numbers. In all cases there is a space between the name and the number. Examples a Anthony 2 Ber 12 Philomena 123. I've looked at LEFT and RIGHT and TRIM but can't see any way to go left from the space or right to the space. Many thanks. ANTHONY |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Finding Specific Text in a Text String | Excel Worksheet Functions | |||
EXTRACT TEXT FROM A DATE | Excel Worksheet Functions | |||
Extract text | Excel Worksheet Functions | |||
Extract % from text string | Excel Worksheet Functions |