udf to extract numeric value [SOLVED]

[dan_l]

So, I've got some really noisy, messy, bad data that I have to 'make useful'. I've been able to do most of it with a combination of a handful of UDF's, standard excel functions, and a CSV editor. One part still has me a little worried. So I've got cells that look like this:


Product Cost

RedWidg et100

BlueWid get200


I need to extract only the numeric portion of the cost field. I've got a UDF (which works, incidentally), I'm just not sure why. Since I'm converting variable types, I thought I would put it past the experts to make sure I'm doing this right:

[pre]

Function ExtNum(target As Range)
Dim ticker As Integer
Dim testchar As String
Dim result As Integer

For ticker = 1 To Len(target)
testchar = Mid(target, ticker, 1)
If IsNumeric(testchar) = True Then
result = result & testchar
Else:  result = result
End If
Next ticker

ExtNum = result
End Function

[/pre]

 

[NARAYANK991]

Hi Dan ,


Can you try this ?

[pre]

Function ExtNum(target As Range)
Dim ticker As Integer
Dim testchar As String

For ticker = 1 To Len(target)
testchar = Mid(target, ticker, 1)
If IsNumeric(testchar) Then Exit For
Next ticker

ExtNum = Val(Right(target, Len(target) - ticker + 1))
End Function

[/pre]
The function you have posted , does not correctly separate decimal numbers.


Narayan

 

[Hui]

Dan_L


You could use a simple formula instead of a UDF

try:

=RIGHT(A1,LEN(A1)-FIND(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),1),A1)+1)

or simplified

=RIGHT(A1,LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&{0;1;2;3;4;5;6;7;8;9}))+1)

 

[Haseeb A]

If you are looking to extract numbers from Right to Left, here is another one,


=LOOKUP(100^100,RIGHT(A1,ROW(A$1:A$15))+0)

 

[dan_l]

thanks fellas.