I have a list of these numbers starting in Column B2: such as
B2
1092813301968
1122915351204
I wish to add a "0" to proceed each value, for it to become a 14 Digit Number
B2
01092813301968
01122915351204
Thank you for any suggestions
I have a list of these numbers starting in Column B2: such as
B2
1092813301968
1122915351204
I wish to add a "0" to proceed each value, for it to become a 14 Digit Number
B2
01092813301968
01122915351204
Thank you for any suggestions
Do you wish the result to be a text string, or do you wish the result to still be a number but be displayed with the leading 0?
To display a number as 14 digits (with leading 0s when the number is smaller than 1E13) but keep the underlying value as a number, use a number format code like "00000000000000" -- https://support.microsoft.com/en-us/...rs=en-us&ad=us
To convert a number to text in a specific number format, use the same number format code in a TEXT() function -- https://support.microsoft.com/en-us/...8-93d29371225c =TEXT(B2,"00000000000000")
Be aware that the number 01092813301968 (even when formatted to display with the leading 0) is not the same as the text string "01092813301968". Users often have other troubles when trying to compare or work with numbers stored as text, so you may want to look into some of those issues so you can avoid them.
Originally Posted by shg
It sort of depends on what you want to happen next... If you want it to look like a 14 digit number...
then apply a custom format of 00000000000000 to the cells.
A little more explanation of what happens NEXT to your shiny new 14 digit number might help....
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
The file I received came in an incorrect format, the new improved (preceeding 0) will just be a "Fixed" value
It affects 1000 values out of 6000 (other values came in correctly and are shorter numbers)..so important to have this value corrected
format the cell
custom format
and add 14 zeros
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Post a small sample sheet showing a representative sample of what you have... and what it should look like afterwards. (5 rows, some of 13 digits, some with fewer.. so we know what you expect to see in BOTH cases).
see yellow banner, top of page, for instructions on posting a sample sheet.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks