Maintaining fractional formatting when combining cells

[adrian078]

Hi,

I want to combine 2 different columns with formatted information.

The first column contains measurements in millimeters(e.g. 15mm). The 2nd column has measurements formatted to fractions (e.g. 5/8). The measurements in the second column are inches.

When I try to combine using the "&":

Please Login or Register  to view this content.

The result is:

Please Login or Register  to view this content.

So when I try to combine it turns my fractional formatting into decimals. Is there a way around this.



I also want to add inverted commas after the inches measurement (eg 5/8"). Is there a way to do this. I've try combining the inches measurement like this:

Please Login or Register  to view this content.

But that doesn't work. Excel just gives me errors.

Any suggestions anyone? I have over 400 of these measurements so I don't really feel like doing it by hand.

Thanks

[shg]

To format cells as mm,

Format > Cells > Mumber, Custom General"mm" or 0.0"mm", or ...

To format as fractions like 2 1/8", use format

# ?/?"''" (that's a double quote, two single quotes, and a double quote)

[adrian078]

To format cells as mm,

Format > Cells > Mumber, Custom General"mm" or 0.0"mm", or ...

Thanks but my cells are already text entries as millimeters

To format as fractions like 2 1/8", use format

# ?/?"''" (that's a double quote, two single quotes, and a double quote)

I tried this and this works Thanks, that's one part of the problem solved.

The next part is that I want to combine the millimeters measurement and the inches in the one cell but excel ignores my fractional formatting when I combine using "&" and converts the inches fraction back to a decimal fraction. So the result is:

23mm 0.625

Where as I want:

23mm 5/8"

Any ideas?

[martindwilson]

=A1&"mm"&" "&TEXT(B1,"# ?/?")&""""

[adrian078]

=A1&"mm"&" "&TEXT(B1,"# ?/?")&""""

Thanks so much for that. That's brilliant!

[adrian078]

Hi,

I've tested this formula further:

Please Login or Register  to view this content.

It works fine except when the converted millimetres is a whole number which is under 10 inches. E.g. if I convert 25mm to inches the resulting formatting is:

50mm (2 ")

Where as I want it to look like the example below without the extra spacing between the " and the 2:

50mm (2")


It works fine if the number in inches is a fraction e.g. 2 5/8"
Or if the number is greater or equal to 10".

Any suggestions how to fix this formatting. Thanks

[shg]

=A1 & "mm " & TEXT(B1, IF(B1 = INT(B1), "0", "# ?/?") ) & """"

[adrian078]

=A1 & "mm " & TEXT(B1, IF(B1 = INT(B1), "0", "# ?/?") ) & """"

Great! Thanks.

So how does this if statement work?

Please Login or Register  to view this content.

[shg]

How about you look at Help for IF and INT, and I'll explain anything that isn't clear after that?