Covert to lbs from Stones and Pounds in the same Cell.

[comic]

I import a file of Weights, Stones and Pounds always into column D2. They have to be imported from the Web as text and always have the (dash) as the Separator.

D2 9-7
D3 8-12
D4 8-11 etc down to D30.

Is there a Macro to convert these into just lbs leaving just this one number in the same Cell?

D2 133
D3 124
D4 123 etc

Thanks

 

[Snakehips]

comic,

Try this...

Sub Convert()
For Each cell In Range("D2:D30")
If (InStr(1, cell, "-")) = 0 Then GoTo NextOne
If Not cell = "" Then
Stones = Left(cell.Text, InStr(1, cell, "-") - 1)
Pounds = Right(cell.Text, Len(cell) - InStr(1, cell, "-"))
cell.Value = (Stones * 14) + Pounds
End If
NextOne:
Next cell
End Sub

Hope that helps.

 

[comic]

Thank you...brilliant!

 

[Andrew Poulsom]

With a formula in E2 copied down:

=(LEFT(D2,FIND("-",D2)-1)*14)+RIGHT(D2,LEN(D2)-FIND("-",D2))