How to format text data to have 6 characters by adding zeros?

Question

I need to format text in cells (excel) that has 2-3 characters (both letters and numbers) to a new cell but the data to have be 6 characters long. I want to add zeros infront of the data. ex. im45 -> 00im45 456->000456 4d4e-> 004d4e id->0000id

Possible duplicate : https://stackoverflow.com/q/3992541/4961700 — Solar Mike, Jul 31 '19 at 11:08

score 2 · Answer 1 · answered Jul 31 '19 at 11:29

lets assume your data starts in B2. Add six 0 to the front of the value of the cell as text and then take the right most 6 characters. This can be done using hte following formula:

=RIGHT("000000"&B2,6)

If you needed more than 6 characters you might want to look into the REPT function.

How to format text data to have 6 characters by adding zeros?

1 Answers1