×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to clean up a concatenated text string?

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.

RE: How to clean up a concatenated text string?

One BF&I approach:
=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?

(OP)
IR,

Thanks, that did the trick.thumbsup

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources