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

 

[RoryA]

Could also use Text to columns, then join back up again with a simple & (with TRIM if needed) formula.

 

[Jyggalag]

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," ",""),","," "))," ",",")

This worked! Amazing!

Thank you so much Peter!!

Also everyone else thank you so much too, I really appreciate all the nice input I got here!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.