Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract number from alphanumric
Hi! Everybody
i have number of alphanumric list For ex. asde1258rfgtt125 iqewr1258erer1258 kpijs780025dfdfd789 And so on how can i extract only numbers For example-1258125 12581258 780025789 Thanks in Advance Hardeep kanwar |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract number from alphanumric
On Jul 2, 9:40*am, Hardeep_kanwar
wrote: Hi! Everybody i have number of alphanumric list For ex. asde1258rfgtt125 * * * * * *iqewr1258erer1258 * * * * * *kpijs780025dfdfd789 And so on how can i extract only numbers For example-1258125 * * * * * * * * * *12581258 * * * * * * * * * *780025789 Thanks in Advance Hardeep kanwar couldn't find a simple spreadsheet formula to do this.... maybe someone else can come up with that Here is a vb function that once inserted in a module can be used on the worksheet. Its a very basic implementation: Function parseInt(text) Dim i As Long Dim ch As String Dim nums As String nums = "" For i = 1 To Len(text) ch = Mid(text, i, 1) If IsNumeric(ch) Then nums = nums & ch Next i parseInt = nums End Function |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract number from alphanumric
Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/ ....then use this formula =REGEX.SUBSTITUTE(A1,"[A-Za-z]","") "Hardeep_kanwar" wrote: Hi! Everybody i have number of alphanumric list For ex. asde1258rfgtt125 iqewr1258erer1258 kpijs780025dfdfd789 And so on how can i extract only numbers For example-1258125 12581258 780025789 Thanks in Advance Hardeep kanwar |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract number from alphanumric
Try this:
Function numit2(r As Range) As String Dim s As String, s2 As String, C As String s2 = "" s = r.Value l = Len(s) For i = 1 To l C = Mid(s, i, 1) If C Like "#" Then s2 = s2 & C End If Next numit2 = s2 End Function Or this: Function stripNumbers(rng As Range) Dim i As Integer For i = 1 To Len(rng.Value) If Mid(rng.Value, i, 1) = "0" And Mid(rng.Value, i, 1) <= "9" Then strNum = strNum & Mid(rng.Value, i, 1) End If Next stripNumbers = CDbl(strNum) End Function Regards, Ryan--- -- RyGuy "Teethless mama" wrote: Download and install the free add-in Morefunc.xll from: http://xcell05.free.fr/english/ ...then use this formula =REGEX.SUBSTITUTE(A1,"[A-Za-z]","") "Hardeep_kanwar" wrote: Hi! Everybody i have number of alphanumric list For ex. asde1258rfgtt125 iqewr1258erer1258 kpijs780025dfdfd789 And so on how can i extract only numbers For example-1258125 12581258 780025789 Thanks in Advance Hardeep kanwar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract the row number | Excel Worksheet Functions | |||
extract number | Excel Discussion (Misc queries) | |||
How do I select the last alphanumric word in an excel column with. | Excel Worksheet Functions | |||
Extract number from text/number string.. | Excel Discussion (Misc queries) | |||
How to extract the Number from a String | New Users to Excel |