Convert text field to number field but keep leading zeros

[voslica]

I need to convert my text data to a number but when I convert using the VALUE function or use "format cells" to the numbers category, I loose the leading zeros. I need to keep them for sorting purposes.

What formula do I use?

 

[WayneRyan]

v,

Format(YourNumber, "000000")

Wayne

 

[RV]

I need to keep them for sorting purposes

There's absolutely no reason why you would require leading zeros for sorting purposes.
On the contrary, you're much better of without them if you want to sort.
Reason being that you would like to convert text to number in order to sort by number.

If you insist on using leading zeros, search the forum, as this question has been asked before, quite a lot actually

RV

 

[voslica]

I need to keep the leading zeros due to it being the employee's ID/SS#.

Also, I already tried the formula below in Excel and it didn't work (and this was the only thread I could find regarding this question)---

=Format(YourNumber, "000000")

When I enter this formula, I receive a #NAME error code.

Thanks for your quick responses!!!!!

 

[WayneRyan]

v,

Change the "YourNumber" to the name of your number field.

Wayne

 

[ejstefl]

I need to keep the leading zeros due to it being the employee's ID/SS#.

Then why change to a number field? If its an ID "number" you shouldn't need to do any math to it, in which case you would be fine to use a text field instead of a number field.

 

[The_Doc_Man]

Specifically if it is a Social Security Number, it is ALWAY a text field despite its use of digits. If SSN won't sort correctly, you need to do data cleansing but this is not an INTEGER vs TEXT issue. I believe you are allowing yourself to be misled by looking at integer vs text sorting.