+ Reply to Thread
Results 1 to 2 of 2

CONCATENATE with IF Function

  1. #1
    Registered User
    Join Date
    11-05-2014
    Location
    Detroit
    MS-Off Ver
    2010
    Posts
    1

    CONCATENATE with IF Function

    Hello All,


    Looking for some help....please!

    So I have 3 columns (A, B & C) that have all been filled from a VLOOKUP function. I would then like to use the CONCATENATE function to combine the three cells into one string(A,B,C). However, some fields in Column B & C are #N/A(due to their being no reference from the VLOOKUP), and the CONCATENATE function will return as #N/A. *FYI-Copied and pasted all as values- no more VLOOKUP formula!*

    I would like to set it up so that if a cell in Column B/Column C does contain #N/A, to just insert as blank during concatenation.

    Here are the three scenarios that could happen...Column A will ALWAYS have text.

    1. Column A contains text, Column B Contains Text, Column C= #N/A
    Output = Column A text - Column B text- Blank
    2. Column A Contains Text, Column B =#N/A, Column C contains text-
    Output= Column A Text - Blank - Column C Text
    3. Only column A contains text, B & C = #N/A
    Output= Columan A Text- Blank - Blank

    I was thinking this formula, but it obviously didn't work since I am on here asking for help!..............................
    =CONCATENATE(B2,"-",=IF(B2=#N/A," ",B2),"-",=IF(C2=#N/A," ",C2))


    Regards,


    Sam.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: CONCATENATE with IF Function

    Try

    =CONCATENATE(B2,"-",=IF(ISNA(B2)," ",B2),"-",=IF(ISNA(C2)," ",C2))
    Martin

+ 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. Using the CONCATENATE function nested in an IF function
    By rottweiler_lvr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2013, 10:02 PM
  2. [SOLVED] Concatenate Function
    By bjelmer in forum Excel General
    Replies: 1
    Last Post: 09-21-2012, 11:11 AM
  3. Concatenate function
    By floricita in forum Excel General
    Replies: 6
    Last Post: 05-24-2010, 10:03 AM
  4. concatenate function
    By Nospam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2006, 08:50 PM
  5. Concatenate function
    By pboost1 in forum Excel General
    Replies: 1
    Last Post: 03-02-2005, 03:52 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