Remove unnecessary commas and space from cells

[Jyggalag]

Hi all,

I currently have a very large data sheet that looks like this:

In this example, I've just put in a format of: Date,Name,Gender

Please note that some of the cells however might only have Date,Gender or Name,Gender or Date,Gender etc.

My ideal format would be: Date,Name,Gender

However, as you can see, many of them have it pasted as "Date,,,, Name ,,,Gender" with a lot of unnecessary commas and spaces

Does anybody know how to remove these duplicate values? Essentially I want all cells to have zero spaces and just one comma inbetween the values (so Date,Name,Gender as the format)

I would truly appreciate some assistance here!

Thank you all!

Kind regards,
Jyggalag

 

[Jon von der Heyden]

Perhaps:

=TEXTJOIN(",",TRUE,IFERROR(SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,"/","."),",","</s><s>")&"</s></t>","//s"),".","/"),""))

* I think there is a TEXTSPLIT function now, but it hasn't been released on my 365 yet.

 

[Tom.Jones]

One way

=TEXTJOIN(",",1,TEXTSPLIT(A1,{","," "}))

 

[Jyggalag]

One way

=TEXTJOIN(",",1,TEXTSPLIT(A1,{","," "}))

Hi,

I tried this, but it does not seem to work...Or am i doing something wrong?

 

[Peter_SSs]

If you don't have those latest TEXT functions yet, try

22 08 12.xlsm
	A	B
1	12/08/2022,George,,,Male	12/08/2022,George,Male
2	11/08/2022,,,Elizabeth,,,Female	11/08/2022,Elizabeth,Female
3	10/08/2022 ,,Thomas ,,Male	10/08/2022,Thomas,Male
4	09/08/2022,,,,,Female	09/08/2022,Female
5	John ,,,,Male	John,Male
Commas

Cell Formulas
Range	Formula
B1:B5	B1	=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1," ",""),","," "))," ",",")

 

[PeteWright]

You could copy your data to a Text Editor that supports Regular Expressions, then do the Replace Job and copy your data back to Excel.

Here is an example how to replace your data to match your requirements with the Notepad++ Text Editor:

Here is the Regular Expression that will find one single row of data:
^(\d\d/\d\d/\d\d\d\d)?[, ]*([a-zA-Z]+)?[, ]*(Male|Female)?.*

And this expression (Notepad++ Syntax) would replace the data to match the requirements:
\1,\2,\3

 

[Peter_SSs]

You could copy your data to a Text Editor that supports Regular Expressions, then do the Replace Job and copy your data back to Excel.

If using Regular Expressions then you could keep it in Excel and do the Regular Expression manipulation there since Excel already supports Regular Expressions via vba.

 

[Jon von der Heyden]

If you don't have those latest TEXT functions yet, try

22 08 12.xlsm
	A	B
1	12/08/2022,George,,,Male	12/08/2022,George,Male
2	11/08/2022,,,Elizabeth,,,Female	11/08/2022,Elizabeth,Female
3	10/08/2022 ,,Thomas ,,Male	10/08/2022,Thomas,Male
4	09/08/2022,,,,,Female	09/08/2022,Female
5	John ,,,,Male	John,Male
Commas

Cell Formulas
Range	Formula
B1:B5	B1	=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1," ",""),","," "))," ",",")

I was a bit worried that the name may actually contain first name and last names with legitimate spaces. Course I'm just speculating

 

[Jon von der Heyden]

Hi,

I tried this, but it does not seem to work...Or am i doing something wrong?

View attachment 71429

I also don't have TEXTSPLIT, but you likely do have TEXTJOIN (I do).

 

[Peter_SSs]

I was a bit worried that the name may actually contain first name and last names with legitimate spaces. Course I'm just speculating

Could be, but I was just going by the given sample data.