Extracting numbers and text

[tkroper]

Hi friends!

I need help extracting numbers from text. In column A, I have a list containing entries like this:

0005 Nurseries — propagation and cultivation of nursery stock
0016 Orchards — citrus and deciduous fruits
0034(1) Poultry Raising
0034(2) Sheep Raising and Hog Farms
0035 Florists — cultivating or gardening

The list goes on for about 800 rows.

In column J, I have this forumula:

{=LEFT(A1,MATCH(2,1/ISNUMBER(MID(A1,ROW(A2:A100)-ROW(A1),1)*1)))}

It works great, except the result includes the left parenthesis: for example, J3 = "0034(1". Is there a way to obtain just the numeric value ("00341")?

Also, is there another formula I can use in column K that will do the opposite and extract only the text? For example: K1 = "Nurseries — propagation and cultivation of nursery stock" and K3 = "Poultry Raising".

Thank you very much for your help!

 

[Sal Paradise]

If the left parentheses is the only character, then:
{=SUBSTITUTE(LEFT(A1,MATCH(2,1/ISNUMBER(MID(A1,ROW(A2:A100)-ROW(A1),1)*1))),"(","")}
(confirmed with Ctrl-Shift-Enter)

 

[jasonb75]

For column K to return the text only try
=(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))
Works with your sample range

 

[tkroper]

For column K to return the text only try
=(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))
Works with your sample range

PERFECT!!! Thank you jasonb75!

 

[tkroper]

If the left parentheses is the only character, then:
{=SUBSTITUTE(LEFT(A1,MATCH(2,1/ISNUMBER(MID(A1,ROW(A2:A100)-ROW(A1),1)*1))),"(","")}
(confirmed with Ctrl-Shift-Enter)

Sal - this works perfectly - except if the entry in column A includes two or more characters:

If column A contains: "5186 Automatic Sprinkler Installation — within buildings — equals or exceeds $27.00", the formula returns: "5186 Automatic Sprinkler Installation — within buildings — equals or exceeds $27.00"

If column A contains: "5187(1) Plumbing — equals or exceeds $24.00", the formula returns: "51871) Plumbing — equals or exceeds $24.00"

In the first example, column A contains two dashes and a dollar sign; in the second example, column A contains a dash and a dollar sign.

Is there a way to fix that?

I sure appreciate your help on this Sal - it's a huge timesaver!

 

[jasonb75]

Todd,

This quick change to Sal's formula seems to work, although it could still bug out on other entries if the numeric code is more than 10 characters including "("

{=SUBSTITUTE(LEFT(A1,MATCH(2,1/ISNUMBER(MID(A1,ROW(A2:A11)-ROW(A1),1)*1))),"(","")}

Entered with CSE as before

Hope this helps

 

[Peter_SSs]

What about avoiding the array formulas for the first part? Does this do the column J job for you?
=SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),"(",""),")","")

Also, a marginally simpler column K formula for you to try:
=REPLACE(A1,1,FIND(" ",A1),"")

 

[tkroper]

Todd,

This quick change to Sal's formula seems to work, although it could still bug out on other entries if the numeric code is more than 10 characters including "("

{=SUBSTITUTE(LEFT(A1,MATCH(2,1/ISNUMBER(MID(A1,ROW(A2:A11)-ROW(A1),1)*1))),"(","")}

Entered with CSE as before

Hope this helps

Works perfectly! Thank you very much jasonb75

 

[tkroper]

What about avoiding the array formulas for the first part? Does this do the column J job for you?
=SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),"(",""),")","")

Also, a marginally simpler column K formula for you to try:
=REPLACE(A1,1,FIND(" ",A1),"")

Peter - thank you very much! Both work perfectly. Your advice is always a tremendous help.

Thank you all for helping me on this

 

[tkroper]

Well, I hope someone can explain this to me...

I needed these formulas because each year the list of codes and their corresponding phraseologies will change somewhat and it is issued on a PDF file. I highlight the list and paste it into an Excel sheet. Then on another sheet in the same workbook I have this formula:

=IF(ISTEXT(A$9),VLOOKUP(A$7,Codes!$B$3:$C$720,2,0),"")

When I enter the code I want in A7 and enter data in A9, it will return the corresponding phraseology from the list in cell A13. I have sorted my list in descending numerical order, but my formula returns "#VALUE!".

If I manually enter any of the codes on the list my formula will work for those codes, but not for codes I have simply pasted into the list. I don't understand - they're all formatted as General.

Is there anything I can do to fix this? Thank you again for your help!