| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from number/text cell
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract text from number/text cell
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 ) -- Biff Microsoft Excel MVP "Access Joe" wrote in message ... 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract text from number/text cell
Thank you guys. Valko - I tried yours first and your function worked
beautifully. So I'm good to go. THANKS! "T. Valko" wrote: 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 ) -- Biff Microsoft Excel MVP "Access Joe" wrote in message ... 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! . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract text from number/text cell
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Access Joe" wrote in message ... Thank you guys. Valko - I tried yours first and your function worked beautifully. So I'm good to go. THANKS! "T. Valko" wrote: 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 ) -- Biff Microsoft Excel MVP "Access Joe" wrote in message ... 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! . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract text from number/text cell
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)) -- If this helps, please remember to click yes. "Access Joe" wrote: 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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract text from number/text cell
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Paul C" wrote: 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)) -- If this helps, please remember to click yes. "Access Joe" wrote: 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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to extract text from number/text cell
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(IND IRECT("1:"&LEN(A1))),1))<=90),0),255) Enter both as CSE functions. HTH, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Paul C" wrote: 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)) -- If this helps, please remember to click yes. "Access Joe" wrote: 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract a number(s) from a text string | Excel Discussion (Misc queries) | |||
| Extract a number from a variable text string | Excel Discussion (Misc queries) | |||
| extract number and use in formula from text & numbers in cell | Excel Worksheet Functions | |||
| Extract number from text/number string.. | Excel Discussion (Misc queries) | |||
| Extract phone number front block of text | Excel Discussion (Misc queries) | |||
Hybrid Mode
