# Extracting numbers and text

#### tkroper

##### Active Member
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:
Code:
``{=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!

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

##### Well-known Member
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

##### Well-known Member
For column K to return the text only try
=(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))

#### tkroper

##### Active Member
For column K to return the text only try
=(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))

PERFECT!!! Thank you jasonb75!

#### tkroper

##### Active Member
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

##### Well-known Member
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

##### MrExcel MVP, Moderator
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

##### Active Member
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

##### Active Member
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

##### Active Member
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:
Code:
``=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!

Replies
1
Views
352
Replies
7
Views
572
Replies
1
Views
6K

1,195,936
Messages
6,012,396
Members
441,695
Latest member
MickRobertson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back