text won't convert to a number

[jscranton]

I have some values like this: Cluster_0052.001.1

I want the numeric portion. I used a formula that worked for most of them =RIGHT(A2,LEN(A2)-8)*1. However, there are about a 1000 where this didn't work. I even tried in VBA and they wouldn't convert (i get a type mistmatch error):

For i = 16105 To 17028


cL = Application.WorksheetFunction.Clean(Trim(Range("D" & i).Value))
nC = Right(cL, Len(cL) - 8)


converted = CDbl(nC)


Range("A" & i).Value = converted


Next i

 

[Rick Rothstein]

I have some values like this: Cluster_0052.001.1

I want the numeric portion.

One example that works does not really give us a clue as to those values that do not work. With that said, perhaps this will do what you want...

Converted = Val(Mid(Cells(i, "D").Value, InStr(Cells(i, "D").Value, "_") + 1))

 

[inactiveuserps07]

Applying this formula

=RIGHT(A2,LEN(A2)-8)*1

to

Cluster_0052.001.1

would result in an error, because 0052.001.1 is not a number (i.e., it has two decimal points) and so cannot be multiplied by 1.

I'm guessing that all of the errors being thrown are a result of this double (or triple, etc.) decimal point, which is not a number and cannot be converted to one. By the same logic, it seems likely that the instances that are not throwing an error are those where only one (or no) decimal point would exist in the truncated result, leaving a valid number.

 

[Rick Rothstein]

One example that works does not really give us a clue as to those values that do not work. With that said, perhaps this will do what you want...

Converted = Val(Mid(Cells(i, "D").Value, InStr(Cells(i, "D").Value, "_") + 1))

The above is a VBA method of pulling the number from the text; here is a worksheet formula that should also work...

=LOOKUP(9.9E+307,--LEFT(MID(A1,FIND("_",A1)+1,99),ROW($1:$99)))