How to insert comma between every character

[MrRAMMounts]

Hi

I would like to know a formula for inserting a comma between every character in the cells of a column.

The cells all have numbers ranging from 1 character to 8 characters (no spaces)

e.g.

11
111111
1121
122
1111122

I would like this to end up as

1,1
1,1,1,1,1,1
1,1,2,1
1,2,2
1,1,1,1,1,2,2

Regards

Jonathan

[Sam Capricci]

you say between 1 and 8 characters, do you have only 1? so that if it is 1 you want to show 1,?

[MrRAMMounts]

Hi

I only require any instance of at least 2 or more characters to be split by a comma.

If it is 1 character, nothing needs to happen

Regards

[tanasedn]

With Power Query

Please Login or Register  to view this content.

[MrRAMMounts]

Hi

Thanks, but is there a normal formula way of achieving this, as I have no idea where to begin with power query.

Regards

[Fluff13]

Are you still using 2016 or do you now have a newer version?

[MrRAMMounts]

you say between 1 and 8 characters, do you have only 1? so that if it is 1 you want to show 1,?

I found this formula which inserts a comma after the second character, but how do I repeat this for every following found character?

=REPLACE(E2,2,,",")

Regards

[MrRAMMounts]

Are you still using 2016 or do you now have a newer version?

I have the latest version of O365 including Excel, as far as I know.

Regards

[Glenn Kennedy]

Check version here:

[Fluff13]

Ok, how about

Please Login or Register  to view this content.

I would also suggest you update your profile

[Glenn Kennedy]

If you have O365.... seen above my redacted email address, this will do it:

=TEXTJOIN(",",,MID(A1,SEQUENCE(LEN(A1)),1))

[MrRAMMounts]

Check version here:

Microsoft 365 MSO version 2210 Build 16.0.15726.20188 32 bit

[Glenn Kennedy]

Ta. Please update your profile... model it on mine.

[MrRAMMounts]

If you have O365.... seen above my redacted email address, this will do it:

=TEXTJOIN(",",,MID(A1,SEQUENCE(LEN(A1)),1))

That almost works!

But it inserts 2 commas between each character for some reason.

1,,1,,1,,1,,1

like that.

Regards

[Glenn Kennedy]

I see only 1.

[tanasedn]

That almost works!

But it inserts 2 commas between each character for some reason.

1,,1,,1,,1,,1

like that.

Regards

It seems that your text have spaces between digits.
In this case use Substitute function

Please Login or Register  to view this content.

[MrRAMMounts]

I see only 1.

All my characters look like this in the cell

111

But when i click in the cell the entry box shows this

1 1 1

Is there some formating issue you think is making it not work on mine?

Regards

[HansDouwe]

Please try

Please Login or Register  to view this content.

[tanasedn]

All my characters look like this in the cell

111

But when i click in the cell the entry box shows this

1 1 1

Is there some formating issue you think is making it not work on mine?

Regards

Then use Substitute like I said before

Please Login or Register  to view this content.

[Fluff13]

It would help if you could supply a workbook, showing the problem.
Especially as 3 people have now posted the same formula, it should work

[Fluff13]

Then use Substitute like I said before

But substitute what, we have no way of knowing what those extra characters are.

[MrRAMMounts]

Please try

Please Login or Register  to view this content.

Perfect that works.

I also had to run =clean() on the column as I think there were some formatting issues there.

Thanks for the help!

Regards

[tanasedn]

But substitute what, we have no way of knowing what those extra characters are.

You're right, just guessing here!

[Fluff13]

All three formulas supplied are the same, so if one works they all should.

I also had to run =clean() on the column

sounds like you have some linefeeds in there. In which case this should also work

Please Login or Register  to view this content.

so that you don't need to clean the column.

[sandy666]

you can read and learn about Power Query here: https://learn.microsoft.com/en-us/power-query/

another way with PQ

Please Login or Register  to view this content.

raw		Commas
11		1,1
111111		1,1,1,1,1,1
1121		1,1,2,1
122		1,2,2
1111122		1,1,1,1,1,2,2

[Glenn Kennedy]

MrRAMMounts... there's a lesson here for you. Always include a SMALL sample sheet. That way confusion is minimised! Maybe not avoided, but minimised.