3

I have a list of numbers that have 3 digits after the dash (FL-100), but in the list are numbers that are formatted with only 2 digits after the dash (AR-12). They all need leading zeroes in order to have 3 digits after the dash. I have tried the TEXT function with no luck because Excel isn't recognizing these as text for some reason. Does anyone have any suggestions to add a leading zero ONLY to the figures with 2 digits after the dash, in a list including figures that are already formatted properly with 3 digits? A sample of the list is attached.

FL-05 

FL-08 


FL-100

FL-101

FL-102

FL-103

FL-104

Thanks in advance, folks!

0

2 Answers 2

2

With data in column A, in B1 enter:

=LEFT(A1,3) & TEXT(--MID(A1,4,9999),"000")

and copy down:

enter image description here

1
  • Dude, this is awesome! Works like a charm! Thanks!!!
    – dfancy22
    Jul 22, 2016 at 14:10
4

If you already have the numbers and the 'FL-' text munched together, you need to split them first, the formula would be =VALUE(MID(cell,4,99)); if they are separately accessible already, you can skip that step.

Next step is to format them for three digits: =TEXT(cell,"000").

Third is concatenating the two pieces back together with &.

All together: =LEFT(cell,3)&TEXT(VALUE(MID(cell,4,99)),"000")

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .