How to clean up a concatenated text string?
How to clean up a concatenated text string?
(OP)
In the attached Excel worksheet, which by the way has mostly <copy> and <paste-special values> because many cells were linked to different worksheets, and has links (not needed, ignore those), I am attempting to eliminate extraneous commas...
Specifically cell I19 - see how it lists GT1AL, GT5L, ,
I want it to show as GT1AL, GT5L
(Note that the cell has the capability of showing up to 4 different GT's - in this example, 2 are shown.
Please - no VBA solutions, I just don't use those...and if no solution exists for this thing, I can live with it.
Specifically cell I19 - see how it lists GT1AL, GT5L, ,
I want it to show as GT1AL, GT5L
(Note that the cell has the capability of showing up to 4 different GT's - in this example, 2 are shown.
Please - no VBA solutions, I just don't use those...and if no solution exists for this thing, I can live with it.





RE: How to clean up a concatenated text string?
=IF(RIGHT(I19,6)=", , ",MID(I19,1,LEN(I19)-6),IF(RIGHT(I19,3)=", ",MID(I19,1,LEN(I19)-3),I19))
which looks for a comma with two spaces pattern.
Likewise, you can do something similar within the CONCATENATE expression directly:
=IF(AS217=" "," ",CONCATENATE(AS217,", ",AS218,IF(AS219=" ","",", "+AS129),IF(AS220=" ","",", "+AS220)))
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: How to clean up a concatenated text string?
Thanks, that did the trick.