How to extract text from number/text cell

[Access Joe]

Excel 2003 - how can I extract JUST the text from cells that contain varying
lengths of numbers and text with no symbol or visible separator? Examples:

182mg/dl
19cm
12938mgl
23854m
1mg/pl

What I would want in the above scenario is this
mg/dl
cm
mgl
m
mg/pl

Is there a way to do this? I've searched everywhere, but almost always
someone has a specific character they are using for the extraction. ANy help
would be greatly appreciated. THANKS!

 

[T. Valko]

One way...

Assuming the numbers on the left are the only numbers in the string.

=MID(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))+1,20)

 

[Paul C]

You have to use a few columns (one more than the longest number), but this
would work

A B C D E F
G H
1 2 3 4 5 6
182mg/dl X X X mg/dl
19cm X X X X cm
12938mgl X mgl
23854m X m
1mg/pl X X X X X mg/pl

For B2-G6 use the formula in B2 and copy over and down
=IF(ISERROR(VALUE(LEFT($A2,B$1))),"X","")

For Column H use this in H2 and copy down
=RIGHT(A2,LEN(A2)-(MATCH("X",B2:G2,0)-1))

 

[ryguy7272]

This should cover all scenarios:

Function RemDigits(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
RemDigits = re.Replace(str, "")
End Function

Call it like this:
=RemDigits(A1)

HTH,
Ryan---

 

[ryguy7272]

Here's a predefined function option:
=MID(A1,MIN(FIND(Letters,UPPER(A1)&Letters)),255)
=MID(A1,MATCH(1,(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))>=65)*(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))<=90),0),255)

Enter both as CSE functions.

HTH,
Ryan--

 

[Access Joe]

Thank you guys. Valko - I tried yours first and your function worked
beautifully. So I'm good to go. THANKS!

 

[T. Valko]

You're welcome. Thanks for the feedback!