+ Reply to Thread
Results 1 to 8 of 8

Concatenate and referencing a blank cell

  1. #1
    Registered User
    Join Date
    01-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    5

    Concatenate and referencing a blank cell

    I have four columns, and they are "studentnumber", "firstname", "middlename" and "lastname" respectively. I am trying to combine the first initial, the middle initial, and 6 characters of the last name into one string of characters. If there is no middle name, I am trying to put the student number in to replace the middle initial. I have gotten the part where I can combine the characters, however I am having problems with substituting the student number for the middle initial if the middlename field is blank. The incorrect formula I have so far is:

    CONCATENATE(LEFT(B2, 1), IF(middlename "", studentnumber, LEFT(C2, 1)) , LEFT(D2, 6))

    I don't know how to reference a blank text cell, and I don't know if simply placing the value of the student number field is sufficient to get the value in there. So, any ideas as to how I need to do this?

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Concatenate and referencing a blank cell

    Can you post a few rows worth of sample data and tell us what results you expect?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Concatenate and referencing a blank cell

    Hi hexr and welcome to the forum

    without seeing what data you are working with, this is untested, but give it a shot...

    =LEFT(B2, 1)& IF(C2= "", studentnumber, LEFT(C2, 1))& LEFT(D2, 6))
    Last edited by FDibbins; 01-20-2013 at 09:24 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Concatenate and referencing a blank cell

    Assuming stuedentnumber is in A2:

    =CONCATENATE(LEFT(B2,1),IF(C2="",A2,LEFT(C2,1)),IF(LEN(D2)>5,LEFT(D2,6),D2))
    or
    =LEFT(B2,1)&IF(C2="",A2,LEFT(C2))&IF(LEN(D2)>5,LEFT(D2,6),D2)

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Concatenate and referencing a blank cell

    You need to remove that comma after the 1st ampersand.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Concatenate and referencing a blank cell

    Thanks Biff, just noticed that as I was reading it again

  7. #7
    Registered User
    Join Date
    01-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Concatenate and referencing a blank cell

    Thanks FDibbins, yours worked for me!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Concatenate and referencing a blank cell

    Happy to help, and thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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