Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 54
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 269
Default 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!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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!

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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!



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 54
Default 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!



.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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!



.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract a number(s) from a text string Christopher770 Excel Discussion (Misc queries) 11 March 23rd 09 03:27 PM
Extract a number from a variable text string tipsy Excel Discussion (Misc queries) 4 May 4th 08 03:28 AM
extract number and use in formula from text & numbers in cell ivory_kitten Excel Worksheet Functions 3 July 14th 06 05:38 AM
Extract number from text/number string.. nastech Excel Discussion (Misc queries) 5 July 5th 06 11:21 PM
Extract phone number front block of text Tech Excel Discussion (Misc queries) 6 August 8th 05 04:07 PM


All times are GMT +1. The time now is 08:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"