Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel won't sort numbers correctly | Excel Discussion (Misc queries) | |||
How to make excel not round real numbers when making a histogram? | Charts and Charting in Excel | |||
How do I extract numbers from many columns in excel on to one cel. | Excel Worksheet Functions | |||
extract numbers, convert to date | Excel Discussion (Misc queries) | |||
Problems with Pivot Table Field Sorting in Excel 2002 | Excel Discussion (Misc queries) |