Extract a number from a text?

**Cygnos** · 03-28-2008, 06:11 AM

Hello folks,

i need to know how to extract a number from text.
Column A looks something like this:

65 25 09-8C St. 35:-
Text text text
75 09 73-8C 1595:-
texttextextextextext
textextextext
75 19 73-8C St. 275:-

I need to extract the price from the different cells... in this example it would be
"35"
""
"1595"
""
""
"275"

NOTE: Not all the prices have a ":-" at the end, which makes this kinda difficult! Help me

**oldchippy** · 03-28-2008, 07:10 AM

Hi,

If the numbers you are looking for are no more than 4 digits long, then this should work for you, put this in B1 and copy down

Please Login or Register  to view this content.

**Cygnos** · 03-28-2008, 07:22 AM

Thanks for your quick reply!
Unfortunately the numbers can be up to 5 digits!
And also this dosn't really work

**oldchippy** · 03-28-2008, 07:26 AM

Hi,

Can you post an example of your data, it may be easier to understand exactly what you are trying to achieve and where the formula doesn't work?

Did it work for any rows?

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

**lecxe** · 03-28-2008, 07:32 AM

Hi,

Try:

Please Login or Register  to view this content.

**Cygnos** · 03-28-2008, 08:08 AM

Ahhh thanks alot man! Works fine!!

**Cygnos** · 03-28-2008, 09:15 AM

Hope i can bother you with another question

=IF(ISNUMBER(FIND(":-";A3));TRIM(RIGHT(SUBSTITUTE(LEFT(A3;FIND(":-";A3)-1);" ";REPT(" ";9));9));"")

this works very well now, but i would like to modify it...
Some prices and with :- but the lower prices are in this format:
12:90
49:90
14:50

Could you add something to the formula that says that IF ":-" can't be found
that it shall look for ":" and also extract the two following digits like :90?
Tried it myself, but I'm not good enough at excel

**lecxe** · 03-28-2008, 10:32 AM

Hi again

If I understand correctly in

65 25 09-8C St. 35:-

you want 35, and in

65 25 09-8C St. 35:12

you want 35:12

If this is correct try:

=IF(ISNUMBER(FIND(":",A1)),TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(":",A1)-1+3*(MID(A1,1+FIND(":",A1),1)<>"-"))," ",REPT(" ",9)),9)),"")

**Cygnos** · 03-28-2008, 10:55 AM

Thanks ALOT!
Works fine!!! =D =D

**printing** · 03-03-2015, 02:50 PM

hello please help I need to extract from one cell to individual cell for each piece of info.

format eg: date store name name1, state price
00/00/00 shoprite springfield S1, NJ 67.64

I would need a cell for each one like date, store name, & price. PLEASE HELP i havebeen stuck on this for over a week. thank you inadvance for any assistance.

**FDibbins** · 03-03-2015, 02:53 PM

Hi, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.