Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to extract numbers from an alphanumeric cell
Is there a function that will read the numbers only of an alphanumeric cell:
Cell that contains a word and number. Appreciate anybody's help in this respect. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to extract numbers from an alphanumeric cell
There may be many ways to do this, but more info would help:
Are the numbers all together or intermixed with alphas? Are they all at the beginning of the string, at the end, or in the middle. Are the strings of consistent length? "diana" wrote: Is there a function that will read the numbers only of an alphanumeric cell: Cell that contains a word and number. Appreciate anybody's help in this respect. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to extract numbers from an alphanumeric cell
On Fri, 10 Mar 2006 06:17:27 -0800, diana
wrote: Is there a function that will read the numbers only of an alphanumeric cell: Cell that contains a word and number. Appreciate anybody's help in this respect. Here's one way that will also give you a bunch of other useful functions. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr Then use this formula: =REGEX.SUBSTITUTE(A1,"[^0-9]") With this "regular expression" anything that is not a number will be replaced with nothing. So the numbers can be any place in the alpha numeric string. [^0-9] matches anything in the string that is not in the range of 0-9. If your needs are different, the expression can be (usually) easily modified to accomplish that. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to extract numbers from an alphanumeric cell
You could use this UDF. It returns a string, so you would use it as
=Value(StripTxt(A1)) If you're new to VBA, read this first: http://www.mvps.org/dmcritchie/excel/getstarted.htm The Function: Function StripTxt(a As String) As String ' Strips all non-numeric characters from a string ' Returns a string, not a number! 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 -- Kind regards, Niek Otten "diana" wrote in message ... Is there a function that will read the numbers only of an alphanumeric cell: Cell that contains a word and number. Appreciate anybody's help in this respect. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to extract numbers from an alphanumeric cell
I was off for the week-end. Hope you check in to find out if I have answered
Niek. Your UDF works like Magic. Many thanks. "Niek Otten" wrote: You could use this UDF. It returns a string, so you would use it as =Value(StripTxt(A1)) If you're new to VBA, read this first: http://www.mvps.org/dmcritchie/excel/getstarted.htm The Function: Function StripTxt(a As String) As String ' Strips all non-numeric characters from a string ' Returns a string, not a number! 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 -- Kind regards, Niek Otten "diana" wrote in message ... Is there a function that will read the numbers only of an alphanumeric cell: Cell that contains a word and number. Appreciate anybody's help in this respect. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a function to extract the URL part of a hyperlink in another cell? | Excel Worksheet Functions | |||
how to extract decimal numbers from alphanumeric strings in Excel | Excel Discussion (Misc queries) | |||
Excel 2000: sum function automated? | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
numbers being entered show in formula bar but not in cell? | Excel Discussion (Misc queries) |