CODEWITHSUNDEEP
excelexcel-formula
jason
jason

Reputation: 4449

adding text in between a string in excel

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?

Upvotes: 1

Views: 3451

Answers (1)

Andy G
Andy G

Reputation: 19367

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)

Upvotes: 3

Related Questions