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

#### comic

##### Board Regular
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

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
comic,

Try this...

Code:
``````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.

Thank you...brilliant!

With a formula in E2 copied down:

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

Replies
0
Views
170
Replies
13
Views
699
Replies
0
Views
195
Replies
0
Views
166
Replies
1
Views
702

1,196,379
Messages
6,014,951
Members
441,857
Latest member
saraguchi2316

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back