CODEWITHSUNDEEP
excel
El Ares
El Ares

Reputation: 1

Excel text formatting

I need some help guys, I tried searching for this but cant find the correct keywords to find the right solution.

So I currently have a column with text values sort of like this:

1A
1B
2A
2C
10A
10B
120A
120B
Etc.

What I need is to format them into something like this

001A-XXXX
....
....
010A-YYY
...
...
120A-ZZZZZ

So, I got the data after the dash figured out. The thing is I need the data before the dash to always be 4 digits in the format shown above. I can for example =concatenate("00", A19, "-") and it works for the single digit numbers like "1A" because it makes it "001A-" however this would make the double and triple digit #s "0010A-" and "00100A-" which I can't have.

Any help on this issue? Thanks in advance for your help and let me know if I need to provide more info, Im not really well versed in excel.

Upvotes: 0

Views: 59

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34230

enter image description hereTry

=TEXT(LEFT(A1,LEN(A1)-1),"000")&RIGHT(A1)

or

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

Upvotes: 0

acousticismX
acousticismX

Reputation: 280

There may be a more efficient way to do this, but you could probably use a nested IF statement with a length function as follows:

=IF(LEN(cellRef)=1, Concatenate("000",cellRef,"-"), IF(LEN(cellRef)=2, Concatenante("00",cellRef,"-"), ...........

Edit: another option is as follows:

=LEFT(cellRef&REPT("0",4),4)

Upvotes: 1

Related Questions