Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for extracting only numeric values.
Any UDF code to have entries like following:
1234Text 12Text34 Text1234 be converted to: 1234 1234 1234 Thanx in advance! -- Best Regards, Faraz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for extracting only numeric values.
The function will return 0 if there is no digits in the string. I can modify
this to return a Null string very easily. Function GetNum(Data As String) GetNum = "" For CharPos = 1 To Len(Data) Char = Mid(Data, CharPos, 1) If IsNumeric(Char) Then GetNum = GetNum & Char End If Next CharPos GetNum = Val(GetNum) End Function "Faraz A. Qureshi" wrote: Any UDF code to have entries like following: 1234Text 12Text34 Text1234 be converted to: 1234 1234 1234 Thanx in advance! -- Best Regards, Faraz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for extracting only numeric values.
Hi,
How about a formula with your string in A1. This is an array formula, see below. Once array entered then drag down as required. With respect to Lars-Ã…ke Aspelin who first posted it. =MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($A$1:$A$300)-1)*ISNUMBER(-MID("01"&A1,ROW($A$1:$A$300),1)),ROW($A$1:$A$300)) +1,1),10^(300-ROW($A$1:$A$300))),2,300) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Faraz A. Qureshi" wrote: Any UDF code to have entries like following: 1234Text 12Text34 Text1234 be converted to: 1234 1234 1234 Thanx in advance! -- Best Regards, Faraz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for extracting only numeric values.
On Fri, 2 Oct 2009 02:54:01 -0700, Faraz A. Qureshi
wrote: Any UDF code to have entries like following: 1234Text 12Text34 Text1234 be converted to: 1234 1234 1234 Thanx in advance! ===================== Function Nums(s As String)as String Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "\D+" Nums = re.Replace(s, "") End Function ======================== Note that by returning the result as String, leading zero's can be retained. If this is not wanted or desireable, then return result as Long or as Double. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TO DELETE ONLY NUMERIC VALUES IN A CHARACTER AND NUMERIC CELL IN | Excel Discussion (Misc queries) | |||
Return Numeric Labels that have different Numeric Values | Excel Worksheet Functions | |||
extracting just numeric value out from alphaneumeric cell | Excel Discussion (Misc queries) | |||
Extracting Numeric Data from a Delimited Text String | Excel Worksheet Functions | |||
Extracting numeric values from string | Excel Worksheet Functions |