Forum Discussion
Formatting of strings of letters/numbers
Hi, I'm hoping someone can help me, I have reports that come through daily with identification numbers that contain a mix of letters and numbers, and I need to format them with dashes to be able to import into another program.
Raw data looks like this:
| HFMRBT0N480020 |
| HFMRBT0J0C0005 |
| HFMRBT0MXJ0010 |
| HFMRBT0NL80006A |
| HFMRBT0NL80006B |
And I need output to be:
HFM-RBT-0N48-0020
HFM-RBT-0J0C-0005
HFM-RBT-0MXJ-0010
HFM-RBT-0NL8-0006A
HFM-RBT-0NL8-0006B
Would anyone have a simple-ish solution??
Thanks a lot!
Amy
With such a text value in A2, enter the following formula in B2, then fill down:
=TEXTJOIN("-", TRUE, LEFT(A2, 3), MID(A2, 4, 3), MID(A2, 7, 4), MID(A2, 11, 100))
