How to retain preceding zeros when converting number stored as text

[Sean15]

Hello:

I have a list of numbers in column C stored as text and left indent. Some of the numbers have preceding zeros.
e.g. 000111, 01212, 001451, 12151, 0000001245

When I convert column C to numbers, Excel loses the preceding zeros and right indents.

How can I convert numbers stored as text to numbers without losing preceding zeros?


Regards,

Sean

 

[Joe4]

You cannot, as leading zeroes hold no significance to numbers (they are meaningless to "numbers").
If the leading zeroes have significance, then what you have is a "string", and not really a "number" (realizing that "strings" can be made up entirely of numbers).

If all your entries were of the same length, you could convert them to numbers, and apply a custom format to them to make them appear as having leading zeroes (even though the underlying values really do not, as it is impossible to store a "number" with leading zeroes).

Can you explain to us exactly what these values are, and how you intend to use them?
Why are you trying to convert them to numbers, if you actually need the leading zeroes?

If we have a better understanding of what it is that you are trying to do, we can give you better direction on how to approach the problem.

 

[Sean15]

Hi:

Column C has invoice numbers and is part of other data downloaded from Microsoft Dynamic AX (worksheet A). I use Index and Match to match invoice numbers from worksheet A and B to return invoice dates from worksheet A. The problem is Index and Match will not work on numbers stored as text. But when I convert column C to numbers, Index and Match returns all required value, but I lose all leading zeros which is part of an invoice number. Make sense? I know leading zero are useless but if there is way I can retain them, and my Index and Match formula still work that would be great.

Regards,

Sean

 

[FlameRetired]

I don't know if this will help or not. It requires Excel 2013 or newer. You can express the text "numbers"/alpha-numerics as base up to base 36.


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

000111​

[/td][td]

01212​

[/td][td]

001451​

[/td][td]

12151​

[/td][td]

0000001245​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

111​

[/td][td]

1212​

[/td][td]

1451​

[/td][td]

12151​

[/td][td]

1245​

[/td][td]

In A2:E2 =DECIMAL(A1,10)​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

000111​

[/td][td]

001212​

[/td][td]

0001451​

[/td][td]

012151​

[/td][td]

00000001245​

[/td][td]

In A3:E3 =BASE(A2,10,LEN(A1))​

[/td][/tr]
[/table]

 

[Joe4]

I use Index and Match to match invoice numbers from worksheet A and B to return invoice dates from worksheet A. The problem is Index and Match will not work on numbers stored as text.

Sure it does. It will work fine on Numbers stored as Text.
However, INDEX/MATCH, like VLOOKUP requires that the values from both lists that you are matching on be set-up the same way (i.e. you cannot compare "numbers entered as text" to "numbers entered as numbers). Many times, due to Custom Formatting, they will look the same, but they are not.

One easy way to tell is to use the ISNUMBER function on an entry from each list. They should both return TRUE (if they are both numbers) or they should both return FALSE (if they are both text). If one is True and one is False, that is your issue. I have a strong suspicion that may be what is going on here, based on what you are reporting.

Can you try that and confirm if that is what is happening here?

 

[Sean15]

Hi:

The values are different. I've converted values in column C to numbers and did a TRIM to remove leading spaces. INDEX/MATCH formula is now returning required values.
It seems that downloaded data were not clean.

Regards,

Sean

 

[Joe4]

It seems that downloaded data were not clean.

That is often the culprit!

Glad to hear you got it working now.