Extracting numbers from text

**bmind** · 06-13-2009, 10:03 PM

See attached file for example.
I need to separate text information from a cell into 3 columns (W,9-8). The first letter I know how to extract. The problem is to extract the number in the middle (1 or 2 digit) between "," and "-" and also the last number. For the last number I could use right() function but the problem is that number could be 1 or 2 digits so I can not specify in advance so formula would have to extract the last number till "-". Anyone knows how to do this?

**vandanavai** · 06-14-2009, 12:18 AM

Hello

i think u can use Delimit.
Try Following
1. Assuming data to be separated is in A1:A4
2. Select range A1:A4
3. Go to option 'Data' -> 'Text to Column'
4. 'Delimited' -> next
5. select 'Delimiters' as 'Other' and put - in delimiter box
6. next
7. select destination cell as B1
8. Then finish
9. now insert one column between column B and C
10.Select Range B1:B4
11 Go to option 'Data' -> 'Text to Column'
12. 'Delimited' -> next
13. select 'Delimiters' as 'Comma'
14. next
15. select destination cell as B1
16. Then finish

I think this will give u desired result.

**JBeaucaire** · 06-14-2009, 01:15 AM

Formula approach...

E3 and copied down:
=SUBSTITUTE(MID(C3,FIND(",",C3)+1,2),"-","")

F3 and copied down:
=RIGHT(C3,LEN(C3)-FIND("-",C3))

**oldchippy** · 06-14-2009, 04:15 AM

Hi,

If you use the formula approach suggested by JBeaucaire this extracts the numbers as text, if you should require these to be avtual numbers you could use

=SUBSTITUTE(MID(C3,FIND(",",C3)+1,2),"-","")*1

or

=VALUE(SUBSTITUTE(MID(C3,FIND(",",C3)+1,2),"-",""))

**bmind** · 06-14-2009, 11:59 PM

Thanks vandanavai for your effort.
JBeaucaire and oldchippy: Formulas work perfectly.
Much appreciated.

**gregddd1** · 02-23-2010, 11:06 PM

The format of the text in which I need to extract numbers is as follows:

23411268 - 23411270

Need to extract the following:

23411268
23411269
23411270

These numbers have to be listed in three seperate rows.

**JBeaucaire** · 02-23-2010, 11:16 PM

Hi Greg, welcome to the forum.

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, rule #2 states that you should start your own thread for your own questions, do not take over someone else's thread with your own topic.

Posting a new thread also insures you get the most attention, anyway. New threads always do.