Text Formula for value within same cell

**srahulkumar** · 01-28-2019, 10:32 AM

Hi All,

I have an situation where i don not want to trim the leading zeros and the cell format should be general not any custom format.

ex : "000000000000469" in general format.

I searched online solutions for this and found i can use text function to achieve this i.e. =TEXT(A1, "000000000000000" ) . But the issue is i cannot get to use the formula within same cell itself. for example i need to use the
formula =TEXT(A1, "000000000000000" ) on the cell A1 itself so that when the user writes in A1 the format will be 000000000000000.

The formula doesn't work as expected if i have the formula in A1 and reference value A1 as well. is there a way i can use the text Formula for value within same cell ?

i am using MS office 2010

**Pranav9940** · 01-28-2019, 10:53 AM

Can you let us know, what is the formula you want to use, and what is expected outcome?
I can help, only if you can give me a detailed question.

**srahulkumar** · 01-28-2019, 11:28 AM

Hi,

Formula want to use = TEXT( A1, "00000000000000")
Where - in cell A1
Cell format - General
Outcome - 00000000000095 ( if the user types 00000000000095 on cell A1 )

MS excel version 2010

**Fluff13** · 01-28-2019, 11:45 AM

You cannot have a formula & a value in a single cell, it's either one or the other.
You either need to put the formula in column B, or you will need a macro

**MrShorty** · 01-28-2019, 11:57 AM

Why does the cell need to be formatted as general? If you preformat the cell as text @, then an entry of 00000000000095 would be treated as text and the leading 0s retained.

If you cannot preformat the cell, is there something happening during data entry that prevents you from prefacing the entry with an apostrophe ('00000000000095) that tells Excel to treat the entry as text?

The key is that you need to do something so that Excel cannot treat the entry as a number. Either of those approaches seem easier than the complications of running a macro.