Repeat characters after certain character

**jam_ran** · 01-23-2017, 06:43 PM

I am looking to add "0" to the beginning of a number until there is a maximum of four digits. For example, 1 -> 0001, 40 ->0040, 623 ->0623. The current formula I have is as follows

Assuming I am looking in cell A1, I have

=IF(LEN(TRIM(A1))<4,REPT("0",4-LEN(TRIM(A1)))&A1,A1)

I have a cell that is "MK-20". How do I modify the formula to have the result be "MK-0020"? Essentially, I would like to apply the the formula after the "-". I know I parse out the data and work from there but I'd like to try to avoid that.

- James

**spitfireblue** · 01-23-2017, 06:51 PM

Maybe something like this?

Formula:

Please Login or Register  to view this content.

**jam_ran** · 01-23-2017, 07:00 PM

Stephen,

This works perfectly. Thanks man

- James

**MrShorty** · 01-23-2017, 07:02 PM

What is your exact starting point for this? A number or another text string?

If I assume you are starting from a number/integer, I might first suggest simple number formatting to get this (number format codes and description: https://support.office.com/en-us/art...2-09fab54be7f4 ). If you have a number (0-9999) in a cell, a number format code like "MK-"0000 will display that number the way you describe. So the number 12 would look like MK-0012. The cell will display MK-0012, but it is important (and maybe useful) to recognize that the underlying cell value is still 12. One could have =A1+1 in the cell underneath to get MK-0013, for example.

Assuming, again, that you are starting with a number, and you want more than just the display changed -- you want the exact text string returned. You can use the TEXT() function (https://support.office.com/en-us/art...8-93d29371225c ) with the same number format code to convert the number 12 to the text string "MK-0012".

If you are not starting with a number, then I would suggest a better description of what you are starting with.