+ Reply to Thread
Results 1 to 7 of 7

If / concatenate

  1. #1
    Registered User
    Join Date
    11-14-2008
    Location
    Austin
    Posts
    3

    If / concatenate

    Hi all,

    Thanks for reading my post. Here is the problem.

    I have 6 columns:

    A1 = LastName
    B1 = FirstName
    C1 = LastName2
    D1 = FirstName2

    I need to concatenate into 1 cell so it looks like this: FirstName LastName;FirstName2 Last Name2

    Now the challenge is that there are many instances where C1 and D1 are blank (don't have values).

    So here was my attempt at a formula:
    =IF(A1>""&B1>"",CONCATENATE(B1," ",A1))& IF(C1>""&D1>"",CONCATENATE(";"&D1,"",C1),"")

    Now this works, but it ALWAYS leaves a semi colon at the end (even when there is no C1 and D1 to concatentate. Thus I put in my IF statement the "".. however this isn't working... maybe this is a lot simpler or I overlooked something.

    Any help greatly appreciated!!
    Last edited by VBA Noob; 11-14-2008 at 04:37 PM.

  2. #2
    Registered User
    Join Date
    11-14-2008
    Location
    Austin
    Posts
    3

    Sorry meant to say " I have 4 columns"

    First sentence correction.. Sorry meant to say " I have 4 columns"

  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Maybe

    =IF(AND(A1="",B1=""),D1&" "&C1,B1&" "&A1&";"&D1&" "&C1)
    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  4. #4
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284
    Try

    =SUBSTITUTE(IF(AND(A1<>"",B1<>""),B1&" "&A1&";","")&IF(AND(C1<>"",D1<>""),D1&" "&C1&";","")&";",";;","")

  5. #5
    Registered User
    Join Date
    11-14-2008
    Location
    Austin
    Posts
    3

    Thanks all!

    Appreciate the suggestions - just decided to go this route.. a little ugly but it works..


    =IF(B1>"",""&B1,"") & IF(A1>""," "&A1,"")& IF(D1>"",";"&D1,"") & IF(C1>""," "&C1,"")


  6. #6
    Registered User
    Join Date
    08-21-2012
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: If / concatenate

    Apologies for highjacking an old thread but I have a similar concatenation issue that probably doesnt warrant a new thread
    .
    I have 2 columns: Unit Number (column B) and Street Number (Column C). I need to join these 2 columns but often there is no Unit Number. I've been trying to adjust the formula mentioned above but I cant seem to get it to work. I have 29555 rows so dont want to do it manually. I will need a / between the Unit Number and Street number e.g. 7/123

    I've tried =IF((B3=""),B3&"/"&C3,B3&"/"&C3), where B is Unit Number and C is Street Number, but when the Unit Number is empty it returns the /Street Number e.g. /16

    Many thanks,
    Adrian

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: If / concatenate

    i think it is better to create another thread for your issue. thank you.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

+ 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