Text and/or Numbers in cell with a conversion formula

Hello, I have created an Inspection Report that is completed in English (Sheet1) and converted to Metric (Sheet2) using the following formula to convert and round:

=IF(ROUND(CONVERT(English!H6,"in","mm"),3)=0,"",ROUND(CONVERT(English!H6,"in","mm"),3))

In Sheet1 the numbers show as 2.0000 or .0020 (example) and in Sheet2 show as 50.800 or .051. Which is what I want the numbers to show as.

Now I need the ability to write text in the same cells in Sheet1, but when I do I get "A value used in the formula is of the wrong data type" in the cells on Sheet2.

How can I get specific number formats with formulas and have the ability to put text in the cell?

Thank you
JG23

Use the TEXT function for numeric values, like this:

="Today is "&TEXT(TODAY(),"dddd")&", and I'm going shopping"

In your case the format string will be "0.000" or ".000" (I think - you show both 4 and 3 decimal places in your examples).

Hope this helps.

Pete

Pete, Thanks for the idea, but it does not work.

I've tried to simplify it by using 2 columns side by side. In column 1 I need to be able to use text or numbers. If there is a number in column 1, I need it to convert to metric in column 2. However, I soon as I put a formula in column 2 to convert it, it no longer excepts text.

Column1 Column2
.0020 .051 (Using Cell*25.4 down column2, numbers are fine but text does not work)
Accept #VALUE

I'm at the end of my rope on this and may just have to do each calculation by hand.

Any other ideas?

Thank you
JG23

Can you post a sample workbook with some examples of exactly what you want to achieve?

Pete