how to use formula to change 8 digit to 10 digit

[sewfoong]

I have a groud of 400 8 digit number , but i need to change it into 10 digit number (like 12345678 become 0012345678) start with 00, how can i use formula to do it? so i no need to manualy change it 400 time. thanks.

[Don]

Hi and welcome to the forum!

This requires a simple concatenation. Assuming your number is in cell A1, use a formula like this:

Code: Select all

="00"&A1

hope this helps

[Sisyphus]

Hi,
I would change the number format for the cell:
From the Toolbar: Format/Cells or Right-click/Format Cells, select the Numbers Tab.
Select "Custom" and enter 10 zeros in the "Type" field, just like this: 0000000000
After formatting one cell in this manner you can paint the format to other cells using the Paint brush. The format will also be available for selection, if you wish to format individual cells.

Cells such formatted will show all numbers with at least 10 digits, even if they had fewer than 8 digits before. You will see this format on the worksheet and in print, when you print the worksheet. However, the value in the formatted cell isn't changed. In Excel you can't have numbers with leading zeros. If you need to present a number in such a format you must change it to a text format. It will then cease to be a number, meaning, you can't use it for calculations any more.

Don's solution is the best if you already have a string (text) and need to add 2 zeros in front. It will also work if you have an alphanumeric number or even a string without any numbers. But if you have a real number to start out with you can convert it using this formula:

Code: Select all

=TEXT(A1, "0000000000")


If A1 contains a number - any numeric value - it will be written with 10 digits, zeros leading as required, fractions disregarded. If it contains text the formula will return whatever it found, unchanged. Use Edit / Paste Special [Values] to convert either Don's or the above formula to the final string that your probably need in your worksheet.
I hope this helps.