Trim / Substitute / Replace / Len - replacing excess spaces with a comma and space - read on!

[manc]

Good day Demigods,

Sheet 1, cell A1 contains alphanumeric user entered data and is referenced on ten other sheets.
Some users enter data as

"E022563, E055634, E074596"

,
Others enter data as, for example,

"E002563", then 5 spaces, "E055634", then 4 spaces, "E074596"

- curse them!

I'm not bothered how it looks on Sheet 1, cell A1, but for the other worksheets, I want the data the user has entered to be displayed as

"E022563, E055634, E074596"

- one comma followed by a space.

I can trim the excess spaces, but don't know how to add the comma.

Any help/comments greatly appreciated, as usual.
Best Regards
manc

 

[markmzz]

Maybe this:

=""""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",,",", "),"""","")&""""

A small modification

=""""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",,",","),",",", "),"""","")&""""

Markmzz

 

[JoeMo]

In an empty cell in sheet1 enter:
=IF(ISNUMBER(SEARCH(",",A1)),TRIM(A1),SUBSTITUTE(TRIM(A1)," ",", "))
and reference this cell instead of A1. This should handle no commas, and excess spaces, but doesn't guard against an entry that is missing some but not all of the commas you want.

 

[markmzz]

Another modification:

=""""&SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," ")),"""","")," ",", ")&""""

Markmzz

 

[manc]

Dear Markmzz and JoeMo,

Thanks very much for your prompt suggestions.
Both work great, although there's less room for user error using markmzz's suggestion.

Thank-you both for your time.
Best Regards
manc

 

[markmzz]

Manc,

I'm happy that your problem was solved.

And thank you for the feedback.

Markmzz