+ Reply to Thread
Results 1 to 8 of 8

If Then CONCATENATE

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    georgia
    MS-Off Ver
    Excel 2003
    Posts
    3

    If Then CONCATENATE

    I have a some names in row A and a some names in Row B. I want to put both names in row C seperated by a semi colon. But, if there are is not a name in A1 and there is a name in B1 I want C1 to show the values of B1 (without the semi colon). If there is a name in A and not in B I want the value of A in C (without a semicolon). If there are names in both A and B I want them both in C with a semi colon. Sorry to be so confusing!

  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,604

    Re: If Then CONCATENATE

    Put this in C1:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and copy down.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    04-11-2013
    Location
    georgia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: If Then CONCATENATE

    I tried that formula -
    When there is a value in B and not A C is: ;(ValueB)
    When there is a value in A and not B C is: (ValueA);
    When there are no values in either C is: ;
    When there is a value in both A and B C is: (Value A)

    But what i want is:
    When there is a value in B and not A C is: (ValueB)
    When there is a value in A and not B C is: (ValueA)
    When there are no values in either C is: (Blank)
    When there is a value in both A and B C isValue A);(Value B)
    Last edited by ewmeach; 08-14-2013 at 01:36 PM.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: If Then CONCATENATE

    Pete's solution works for me.

    Are you sure your blanks are really blank?
    Check them for spaces or other wierd characters.

    Post a sample book.


    Here's another solution as well.

    =A1&IF(COUNTA(A1,B1)=2,";","")&B1

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

    Re: If Then CONCATENATE

    Works for me - see attached file.

    Pete
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-11-2013
    Location
    georgia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: If Then CONCATENATE

    Screen Shot 2013-08-14 at 1.58.08 PM.png
    South = F
    North = G
    People = H
    This is the equation for H2=IF(F2="","",F2) & IF(G2<>"",IF(F2="","","; ")&G2,"")
    then filled down

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

    Re: If Then CONCATENATE

    No, scrub that, sorry.

    Pete

    EDIT: you must have a space in F2. Select that cell and press the Delete key.

    Pete
    Last edited by Pete_UK; 08-14-2013 at 02:15 PM.

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

    Re: If Then CONCATENATE

    Okay, belt and braces now - if you do have spaces instead of empty cells, then you can use this in H2:

    =IF(TRIM(F2)="","",TRIM(F2)) & IF(TRIM(G2)<>"",IF(TRIM(F2)="","","; ")&TRIM(G2),"")

    and copy down.

    Hope this helps.

    Pete

+ 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. Replies: 2
    Last Post: 04-12-2010, 12:35 PM
  2. Un - Concatenate?
    By Kassie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 07:05 AM
  3. [SOLVED] concatenate
    By jeanette.rimmer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  4. IF, IF, Concatenate?
    By savvysam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  5. [SOLVED] [SOLVED] I know how to concatenate ,can one de-concatenate to split date?
    By QUICK BOOKS PROBLEM- in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-26-2005, 01:05 PM

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