Number Formatting in a cell with a formula

[tonyjyoo]

Hello,

I have cells in column A that are directly linked to other cells in a different tab.

For example, cell A1 =

='Tab2'A1

However, I am trying to keep the number formatting so that my numbers have a leading 0 (8 digits total).

For example, "920" should be reading "09200000". However, because the cell is a formula, it is spitting out "9200000". When I type it manually, the number format shows correctly.

Any thoughts as to how to approach this?

Thanks.

 

[ITWare2008]

Hi Tonyjyoo,

I'm wondering how you write 920 and get 9200000?

With a custom format as 00000000 I get 00000920 when I write 920.

Vandalo

 

[tonyjyoo]

Hi Tonyjyoo,

I'm wondering how you write 920 and get 9200000?

With a custom format as 00000000 I get 00000920 when I write 920.

Vandalo

Sorry, I meant to say I'm writing "9200000", and it should be giving me "09200000", but if the cell has a formula, it only reads as "9200000", ignoring my number format.

 

[Joe4]

Sorry, I meant to say I'm writing "9200000", and it should be giving me "09200000", but if the cell has a formula, it only reads as "9200000", ignoring my number format.

Number formats are only applied to numbers, not text (or text entered like numbers). So you need to coerce your formula to return a number, and not text.

If your formula is:

='Tab2'A1

one way to coerce it to a number would be like this:
If your formula is:

='Tab2'A1 * 1

 

[tonyjyoo]

Number formats are only applied to numbers, not text (or text entered like numbers). So you need to coerce your formula to return a number, and not text.

If your formula is:

='Tab2'A1

one way to coerce it to a number would be like this:
If your formula is:

='Tab2'A1 * 1

Absolutely brilliant. This is exactly what I was looking for.

Thank you!

 

[Joe4]

You are welcome!