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!

 

[Peter_SSs]

I have been unable to reproduce the problem.

 

[Sal Paradise]

They could have odd characters caught inside the strings, or have leading/trailing spaces, or have some as text/some as numbers, or a slew of other potential issues. If you're just copy-pasting, it could be any number of things. Type in an entry manually, and then do =A1=B1 to compare. If it says 'FALSE' then your data probably isn't formatted as expected.

 

[tkroper]

Well, let's see...I think I stumbled onto the solution. I copied the list containing the formula results and pasted them as values into the range on my Codes sheet. I used Sal's =A1=B1 suggestion to compare my newly pasted list of values to the list containing the formulas and it returned "TRUE". But in that process I noticed the little green triangles in the upper left corner of the newly pasted cells, opened the menu and selected "Convert to number" from the menu and now it works. I didn't realize copying the list containing formulas and pasting them as values converted the numeric results of the formulas to text (there were no leading apostrophes in the cells).

Sorry for the trouble on this one - I'm still fairly ignorant about Excel because I'm self-taught on-the-job. Almost all that I've been able to accomplish improving this terribly conceived worksheet that I'm forced to use daily at work is due to the generous help of my friends here on Mr. Excel. I'm very grateful for all the help you have given me, and I appreciate the kindness of so many people with expertise far exceeding mine.

 

[Peter_SSs]

Well, let's see...I think I stumbled onto the solution. I copied the list containing the formula results and pasted them as values into the range on my Codes sheet. I used Sal's =A1=B1 suggestion to compare my newly pasted list of values to the list containing the formulas and it returned "TRUE". But in that process I noticed the little green triangles in the upper left corner of the newly pasted cells, opened the menu and selected "Convert to number" from the menu and now it works. I didn't realize copying the list containing formulas and pasting them as values converted the numeric results of the formulas to text (there were no leading apostrophes in the cells).

Sorry for the trouble on this one - I'm still fairly ignorant about Excel because I'm self-taught on-the-job. Almost all that I've been able to accomplish improving this terribly conceived worksheet that I'm forced to use daily at work is due to the generous help of my friends here on Mr. Excel. I'm very grateful for all the help you have given me, and I appreciate the kindness of so many people with expertise far exceeding mine.

Hmm, given this extra information I still can't reproduce the problem.

Maybe I am not understanding which cells you are referring to, but with this formula ...
=IF(ISTEXT(A$9),VLOOKUP(A$7,Codes!$B$3:$C$720,2,0),"")
... if I put a number in A7 and numbers stored as text in 'Codes' column B, then my formula returns #N/A, not #VALUE!

The only way I have been able to produce #VALUE! is to either

a) change the third argument of the VLOOKUP from 2 to a number less than 1 (clearly you haven't done that), or

b) put a formula in 'Codes' column C that produces a #VALUE! error so that the VLOOKUP is just returning what it finds in column C, or

c) put a formula in cell A9 that returns a #VALUE! error


Anyway, glad you seem to be sorted.

 

[stanleydgromjr]

tkroper,

Try:

Excel Workbook
	A	B
1	0005 Nurseries propagation and cultivation of nursery stock	0005
2	0016 Orchards citrus and deciduous fruits	0016
3	0034(1) Poultry Raising	00341
4	0034(2) Sheep Raising and Hog Farms	00342
5	0035 Florists cultivating or gardening	0035
Sheet1

Option Explicit
Function GetChars(target As Range)
' thomach
' http://www.ozgrid.com/forum/showthread.php?t=63632
'
    Dim MyStr As String, i As Integer
    MyStr = ""
    If Len(target.Value) = 0 Then GoTo GoExit
    For i = 1 To Len(target.Value)
        If Not IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)
    Next i
GoExit:
    GetChars = MyStr
End Function
 
Function GetNums(target As Range)
' thomach
' http://www.ozgrid.com/forum/showthread.php?t=63632
'
    Dim MyStr As String, i As Integer
    MyStr = ""
    If Len(target.Value) = 0 Then GoTo GoExit
    For i = 1 To Len(target.Value)
        If IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)
    Next i
GoExit:
    GetNums = MyStr
End Function

Have a great day,
Stan

 

[stanleydgromjr]

tkroper,

Try:
http://www.mrexcel.com/forum/showthread.php?t=362461


Have a great day,
Stan