+ Reply to Thread
Results 1 to 3 of 3

How to Concatenate 3 or more cells with variable inputs

  1. #1
    Registered User
    Join Date
    03-05-2013
    Location
    Northern Calif, USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Question How to Concatenate 3 or more cells with variable inputs

    I am new to the forum so please bear with me if I indirectly break any rules......

    I am trying to concatenate 3 (or more) cells which contain text in Excel 2010. I use the formula =CONCATENATE(A2," ", B2," ",C2," ",D2) or =A6&" "&B6&" "&C6&" "&D6. My issue is that I don't always have data in all the fields and I have an extra space in the concatenated name when C3 (the middle field) is blank. Is there a way to have a variable command to insert a space when there is text but to not insert the extra space when there is nothing in the referenced cell or remove the extra spaces in my named range/cell? I need 1 space in between each word but if there is no word in the middle field(s) then there is an extra space.

    Any help would be appreciated.

    For example

    Excel Concatenation Question.xlsx

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: How to Concatenate 3 or more cells with variable inputs

    hi pblobe, welcome to the forum. TRIM will do the job for you.
    =TRIM(CONCATENATE(A2," ", B2," ",C2," ",D2))

    =TRIM(A6&" "&B6&" "&C6&" "&D6)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    03-05-2013
    Location
    Northern Calif, USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: How to Concatenate 3 or more cells with variable inputs

    Thank you so so much. You are AWESOME. I love Excel and this forum is great. I hope it is ok to say Thank you. Gotta find those rules again....

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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