I am currently using Microsoft Excel 2011 for Mac. I am trying to separate data in the following format with delimiters. So far I have not been able to come up with a practical solution. Can anyone provide me with one?

The text is in the format below.

WPD0114

ALC0414

CBC0515

IUC0515

IEI1216

I would like the text to look like this.

WPD,01,14

ALC,04,14

CBC,05,15

IUC,05,15

IEI,12,16

I apologise if a similar answer has been provided previously, but I have not been able to find a working solution.

up vote 3 down vote accepted

Split your strings up using LEFT(), MID() and RIGHT() and concatenate with commas.

=LEFT(A1,3)&","&MID(A1,4,2)&","&RIGHT(A1,2)
  • 2
    This is the easiest way to do this that I can think of, assuming the text length is always the same. If you were to only need to insert one character, I'd suggest =replace(A1,3,0,",") and if you were trying to actually replace, I'd say nested substitute functions. This is the answer for this scenario. – Raystafarian Jan 15 '14 at 11:28
  • 1
    Never thought of doing a replace() with 0 for the Num_chars argument. Very clever! – Andi Mohr Jan 15 '14 at 11:44

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.