Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Brian
 
Posts: n/a
Default Extract only numbers from an alphanumeric field in Excel?

I am trying to extract only the numbers from an alphanumeric field. The
numbers are not consistently in the same spot. Example: abc123, 123abc or
a123bc... I want a returned value of "123".

Thank you


  #2   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default Extract only numbers from an alphanumeric field in Excel?

Hi Brian,

You could use this UDF (User defined Function):

' ================================================== ===========================

Function StripTxt(a As String) As String

' Niek Otten, March 22 2006

' Strips all non-numeric characters from a string, but leaves any decimal separator

' Returns a string, not a number!

' If you need a number, use =Value(StripTxt(...))



Dim i As Long

Dim b As String

For i = 1 To Len(a)

b = Mid$(a, i, 1)

If ((Asc(b) 47 And Asc(b) < 58) Or b = Application.DecimalSeparator) Then StripTxt = StripTxt + b

Next i

End Function

' ================================================== ===========================



If you don't know (yet) how to implement a UDF:



================================================

Pasting a User Defined Function (UDF)

Niek Otten, March 31, 2006



If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these steps:



Select all the text of the function. CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut
for Copy.

Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).

From the menu bar, choose InsertModule. There should now be a blank module sheet in front of you. Click in it and then press
CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.

Press ALT+F11 again to return to your Excel worksheet.

You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)

================================================




--
Kind regards,

Niek Otten


"Brian" wrote in message ...
I am trying to extract only the numbers from an alphanumeric field. The
numbers are not consistently in the same spot. Example: abc123, 123abc or
a123bc... I want a returned value of "123".

Thank you




  #3   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default Extract only numbers from an alphanumeric field in Excel?

On Mon, 3 Apr 2006 11:01:02 -0700, Brian
wrote:

I am trying to extract only the numbers from an alphanumeric field. The
numbers are not consistently in the same spot. Example: abc123, 123abc or
a123bc... I want a returned value of "123".

Thank you


Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the Regular Expression formula:

=REGEX.SUBSTITUTE(A1,"\D")

The expression "\D" matches anything in the string that is not a digit [0-9].
The substitute function substitutes the non-digits with a null, leaving only
digits.



--ron
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
Excel won't sort numbers correctly martin0642 Excel Discussion (Misc queries) 2 September 27th 05 05:34 PM
How to make excel not round real numbers when making a histogram? Leedawg Charts and Charting in Excel 1 September 21st 05 07:36 PM
How do I extract numbers from many columns in excel on to one cel. DFALUSA Excel Worksheet Functions 3 March 3rd 05 02:48 AM
extract numbers, convert to date gkaspen Excel Discussion (Misc queries) 7 March 2nd 05 03:31 AM
Problems with Pivot Table Field Sorting in Excel 2002 Phoenix71555 Excel Discussion (Misc queries) 1 February 28th 05 12:25 AM


All times are GMT +1. The time now is 02:12 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"