+ Reply to Thread
Results 1 to 10 of 10

Concatenate and remove extraneous characters at the end

  1. #1
    Registered User
    Join Date
    07-24-2017
    Location
    Irvine, California
    MS-Off Ver
    2013
    Posts
    16

    Concatenate and remove extraneous characters at the end

    I want to Concatenate a series of choices the user will make from six cells containing lists.

    So I added the formula: =J5&"; "&J6&"; "&J7&"; "&J8&"; "&J9&"; "&J10
    I added the semicolon and space so each choice would be separated.
    Ex. Item A; Item B; Item D; and so on.

    However the user may only need to one or two items. So the Concatenated cell would look like:
    Item A; Item B; ; ; ;

    How can I remove the extra semicolons and spaces at the end?
    I would like to avoid a Macro if possible...

    Thanks in advance.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Concatenate and remove extraneous characters at the end

    Hi blackcloud9 and welcome to the forum,

    If you use a last step to "Substitute" all double ";;" with a blank, that may do what you need.

    See the formula of:
    Please Login or Register  to view this content.
    in the attached.
    Blank extra ending characters.xlsx
    Last edited by MarvinP; 07-24-2017 at 03:30 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    07-24-2017
    Location
    Irvine, California
    MS-Off Ver
    2013
    Posts
    16

    Re: Concatenate and remove extraneous characters at the end

    Marvin,

    Tried that and initially it worked. But that was because there two pairs of semicolons ("; ;") to be substituted.
    However, if the user did three choices, it left an odd number of semicolons to be removed.
    So the result looked like:
    Item A; Item B; Item D ; - the last ";" being out of place.

    Thanks for your help, though

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Concatenate and remove extraneous characters at the end

    I updated my first answer. See the formula and attached workbook to see if this does what you want. Did you have spaces between your semicolons??

  5. #5
    Registered User
    Join Date
    07-24-2017
    Location
    Irvine, California
    MS-Off Ver
    2013
    Posts
    16

    Re: Concatenate and remove extraneous characters at the end

    Thanks for the help I'll try it out.

  6. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,843

    Re: Concatenate and remove extraneous characters at the end

    I assume you don't have Excel 2016 ( it doesn't show on mobile device) but FYI this very nice function is in your future: https://support.office.com/en-us/art...3-0e8fc845691c


    UDF is always an option, I made a small one somewhere in this thread:
    https://www.excelforum.com/tips-and-...avatar.htmlion is available.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Concatenate and remove extraneous characters at the end

    The TEXTJOIN function is only available in the Office 365 subscription version of Excel 2016 at the moment.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Concatenate and remove extraneous characters at the end

    Hi,

    For that many cells, I'd adopt a simple brute force approach
    =J5&IF(J6="","","; "&J6)&IF(J7="","","; "&J7)&IF(J8="","","; "&J8)&IF(J9="","","; "&J9)&IF(J10="","","; "&J10)
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  9. #9
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,899

    Re: Concatenate and remove extraneous characters at the end

    Or try this ...

    =MID(IF(J5="","","; "&J5)&IF(J6="","","; "&J6)&IF(J7="","","; "&J7)&IF(J8="","","; "&J8)&IF(J9="","","; "&J9)&IF(J10="","","; "&J10),3,2000)

  10. #10
    Registered User
    Join Date
    07-24-2017
    Location
    Irvine, California
    MS-Off Ver
    2013
    Posts
    16

    Re: Concatenate and remove extraneous characters at the end

    Thank you all for your help.
    I chose "xlnitwit" and "Phuocam" solutions to resolve my problem. I "clicked" your respective stars.
    They both work. I'm trying to decide which one to use.
    I'm trying to decipher how each one works.
    It's good to have a solution. It's far better if you know how that solution works

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Remove characters from string from the right after 4 characters
    By dlemley in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-10-2017, 08:47 AM
  2. Auto concatenate and reverse concatenate & Remove duplicates .
    By Andy308 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-15-2016, 12:38 PM
  3. Remove Text characters, leaving numerical characters
    By aschwalge in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-01-2016, 06:42 PM
  4. [SOLVED] Concatenate all but Special Characters
    By jomili in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-28-2015, 05:48 PM
  5. [SOLVED] VBA, remove all characters within a string, except numerical characters and full stops
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2015, 09:24 PM
  6. Concatenate & Special characters
    By dgeehot in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-23-2013, 04:58 PM
  7. Remove extraneous rows
    By h2o2sail in forum Excel General
    Replies: 6
    Last Post: 05-27-2010, 12:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1