Microsoft 365 Excel Convert Numbers Loaded as Text with Leading Zeros

[GailT917]

I have a file that imported that has numbers on it with leading zeros. The number was imported as text. How do I get B000123456 when I have 000123456 that came over?

[#2]

@GailT917 

To convert numbers loaded as text with leading zeros into numbers with leading zeros, you can use the TEXT function in Excel. Assuming your original data (000123456) is in cell A1. In an empty cell (e.g., B1), enter the following formula:

=TEXT(A1,"000000000")

If the leading zeros are not always at the front, and you want to retain only the leading zeros (if any) while converting the rest of the digits to numbers, you can use a combination of functions like LEFT, MID, and VALUE, enter the following formula:

=LEFT(A1, LEN(A1) - LEN(VALUE(A1))) & VALUE(A1)

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

@GailT917 

If you are on insider or Current channel you may uncheck default settings in File->Options

Above is for Windows. Not sure about other planforms and channels.