Results 1 to 8 of 8

Thread: Create formula that will merge data in multiple columns (A, B, C and D) in that order

  1. #1
    Seeker watermt69's Avatar
    Join Date
    Nov 2014
    Posts
    11
    Articles
    0
    Excel Version
    2010

    Create formula that will merge data in multiple columns (A, B, C and D) in that order



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need to create a formula that will merge data in multiple columns (A, B, C and D) in that order. It should also yield an Equipment ID# that looks exactly like the text in the highlighted cell of the attached spreadsheet. I'm being asked to ensure the leading zeros (0) show in the Equipment ID# automatically. Then I'm to copy the formula down from E2 to E83 (this I believe I can handle.

    Can anyone assist me on this task?
    Attached Files Attached Files

  2. #2
    Neophyte spiceytayley's Avatar
    Join Date
    May 2019
    Posts
    4
    Articles
    0
    Excel Version
    Microsoft Excel 2010
    =Table1[@Building]&"-"&Table1[@Equipment]&"-00"&Table1[@Number]&"-"&Table1[@Location]

  3. #3
    Neophyte spiceytayley's Avatar
    Join Date
    May 2019
    Posts
    4
    Articles
    0
    Excel Version
    Microsoft Excel 2010

    Completed Sheet:

    See attached.XGURU.xlsx

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,537
    Articles
    0
    Excel Version
    365
    Try:
    =TEXTJOIN("-",,A2,B2,TEXT(C2,"000"),TEXT(D2,"000"))
    or
    =A3&"-"&B3&"-"&TEXT(C3,"000")&"-"&TEXT(D3,"000")

  5. #5
    Seeker watermt69's Avatar
    Join Date
    Nov 2014
    Posts
    11
    Articles
    0
    Excel Version
    2010
    Works like a charm, thanks a million!

  6. #6
    Seeker watermt69's Avatar
    Join Date
    Nov 2014
    Posts
    11
    Articles
    0
    Excel Version
    2010
    The second formula worked very well, but all I could get returned for the first formula was #NAME?. Even after making sure the row numbers lined up correctly.

  7. #7
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    144
    Articles
    0
    Excel Version
    2019
    all I could get returned for the first formula was #NAME?.
    TEXTJOIN is not a function available to Excel 2010 which you have listed as your version.

  8. #8
    Seeker watermt69's Avatar
    Join Date
    Nov 2014
    Posts
    11
    Articles
    0
    Excel Version
    2010
    Thanks for the clarification and assistance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •