+ Reply to Thread
Results 1 to 4 of 4

Formula to concatenate two cell values only when both cells contain text

  1. #1
    Registered User
    Join Date
    10-17-2020
    Location
    USA
    MS-Off Ver
    2007
    Posts
    10

    Formula to concatenate two cell values only when both cells contain text

    Dear all,

    I tried to concatenate two cell values and intersperse the two cell values with a semicolon only when both cells contain text; in Excel 2007, using formulas only.

    Interestingly, the following did NOT work:
    =IF(ISBLANK(TRIM(E2)),IF(ISBLANK(TRIM(Q2)),"",Q2),IF(ISBLANK(TRIM(Q2)),E2,E2&";"&Q2))
    as it apparently evaluates all the IF statements as FALSE, independent of the contents of the cells E2 and Q2.
    Although it does not produce an error message,
    t looks like one can NOT meaningfully combine ISBLANK and TRIM in this nested way,
    nor can one meaningfully combine ISBLANK and CLEAN in a similarly nested way.

    I therefore have two questions:
    1. Is there is a formula to perform the conditional concatenation if cells contain blanks or other invisible characters ?
    2. Why can one not meaningfully nest TRIM or CLEAN within ISBLANK ?

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Formula to concatenate two cell values only when both cells contain text

    Try it like this:

    =IF(TRIM(E2&Q2)="","",IF(TRIM(E2)="",Q2,IF(TRIM(Q2)="",E2,E2&";"&Q2)))

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    10-17-2020
    Location
    USA
    MS-Off Ver
    2007
    Posts
    10

    Re: Formula to concatenate two cell values only when both cells contain text

    That helps a lot, Pete !
    Thanks for your excellent solution !

  4. #4
    Registered User
    Join Date
    10-17-2020
    Location
    USA
    MS-Off Ver
    2007
    Posts
    10

    Re: Formula to concatenate two cell values only when both cells contain text

    I'm still curious as to why combining the ISBLANK and TRIM / CLEAN functions doesn't work.

+ 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. Test cell ISBLANK, if true concatenate two text cells
    By Booleanboy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-21-2020, 03:37 AM
  2. Concatenate cell with values retained in cells
    By AmazingTrans in forum Excel General
    Replies: 9
    Last Post: 02-09-2018, 07:48 PM
  3. [SOLVED] Concatenate values from Text Boxes into 1 cell not working
    By moosmahna in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2017, 05:20 AM
  4. [SOLVED] Formula to total sum of cell values when cells contain both text & numbers
    By Rogerjw in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-15-2017, 07:27 AM
  5. Concatenate multiple cell values based on matching adjacent cells
    By mkrzy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2013, 02:40 AM
  6. Macro to concatenate or add text to cell value based on another cells value
    By kstrick99999 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 01:00 PM
  7. [SOLVED] Can I concatenate text in cells to make a working formula?
    By Matt S. R. in forum Excel General
    Replies: 11
    Last Post: 11-11-2005, 11:50 AM

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