CODEWITHSUNDEEP
excelvba
Adige72
Adige72

Reputation: 177

Convert a value from cm to inch in excel

A number in centimeter format can be converted to inch using this function:

=CONVERT(A1,"cm","in")

What i want is that instead of replacing the number, putting the converted number next to original one. Actually it's a more complicated situation:

Original cell:

35 - 45 cm

After convertion (all in one cell):

35 - 45 cm
(14 - 18 inch)

So, how to do this in excel in an automated way since there are many cells in this format, maybe using a macro?

Upvotes: 1

Views: 12059

Answers (2)

Tim Williams
Tim Williams

Reputation: 166540

Formula approach - if your values start in A1 then place this in B1 and fill down.

= A1 & CHAR(10) & "(" & ROUND(CONVERT(
VALUE(LEFT(A1,SEARCH("-",A1)-1)),"cm","in"),1) & " - " &
ROUND(CONVERT(
VALUE(MID(SUBSTITUTE(A1,"cm",""), SEARCH("-",A1)+1,10)),"cm","in"),1) & "in)"

Upvotes: 1

user2140261
user2140261

Reputation: 7993

I will start with this but I Suggest providing me more information so I can better Solve your problem: 

Sub AddConversionToCell()

    FirstNumberAsIn = WorksheetFunction.Convert(Left(Range("A1").Value, 2), "cm", "in")
    SecondNumberAsIn = WorksheetFunction.Convert(Mid(Range("A1").Value, 6, 2), "cm", "in")
    Range("A1").Value = Range("A1").Value & " ( " & FirstNumberAsIn & " - " & SecondNumberAsIn & " inch)"

End Sub

Upvotes: 0

Related Questions