adding text in between a string in excel

Question

I have a string that looks like this in one cell:

Col1Col2Col3Col4Col5

it goes all the way to Col100.

I want to use one formula without VBA and add a comma in between the Col to make the text look like this:

Col1,Col2,Col3,Col4,Col5

again all the way to Col100.

I'm thinking some kind of find replace function in excel?

Andy G · Accepted Answer · 2013-07-02 01:12:50Z

3

As a formula:

=LEFT(A1,1)&SUBSTITUTE(MID(A1,2,1024),"Col",",Col")

1024 is an assumed maximum number of characters in a cell.

If not every cell contains this text then you could only substitute if the cell begins with "Col":

=IF(LEFT(A1,3)="Col",LEFT(A1,1)&SUBSTITUTE(MID(A1,2,255),"Col",",Col"),A1)

answered Jul 2, 2013 at 1:05

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sweet. thanks. quick question, what is the "255" for in the mid function? is that arbitrary?

Yes, I just made it up. But if your cells go up to Col100 then use 1000.

Another way would be to replace every Col with ,Col and then eliminate the first comma - =SUBSTITUTE(SUBSTITUTE(A1,"Col",",Col"),",","",1)