Convert cell contents from millimeters to inches

[Tosty]

Good morning. I have an Excel spreadsheet that was sent to me that contains dimensions in millimeters, but my boss would prefer that the contents were in inches. Is there an easy way to convert the contents of all the cells from millimeters to inches or format them so they change to inches?

I know I can insert parallel columns and put a formula in each cell corresponding to the cell next to it like "=(B2/25.4)" which will convert the contents of B2 to inches, but it is time-consuming and I was hoping their might be a simpler method. Thank you in advance for any assistance you can provide.

Respectfully,
Tosty

 

[VoG]

If you put that in row 2, you can drag the formula down the column.

 

[Tosty]

Thanks for the reply Peter. That's what I am currently doing, I was just hoping for a more "global" solution to format all the cells at once.

Best regards,
Tosty

 

[VoG]

Try

Sub mmtoinch()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("B" & i)
        .Value = .Value / 25.4
    End With
Next i
End Sub

 

[PA HS Teacher]

How about, entering 25.4 in a blank cell.

Copy this cell.

Highlight the metric cells.
Edit, Paste Special, Check Divide

None of these solutions will in and of themselves format the results in feet Inches. Just feet with a decimal for the remainder.

 

[Tosty]

VoG & PA HS Teacher,

Thank you both for your suggestions. Since I am completely unfamiliar with Visual Basic I will probably use PA HS Teacher's solution. It worked well on a couple test cells I tried. The only additional thing I'll have to do is to format the cells after I convert them to include only 3 decimal places, as it takes it out too many decimal places. I tried formatting them to 3 decimal places before I converted them, but the formatting didn't hold. Other than that it seems to do what I need it to do. Thank you both for your kind attention, it is much appreciated!

Best regards,
Tosty