PDA

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



watermt69
2019-05-15, 07:15 PM
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?

spiceytayley
2019-05-15, 08:23 PM
=Table1[@Building]&"-"&Table1[@Equipment]&"-00"&Table1[@Number]&"-"&Table1[@Location]

spiceytayley
2019-05-15, 08:44 PM
See attached.9113

p45cal
2019-05-16, 03:29 PM
Try:
=TEXTJOIN("-",,A2,B2,TEXT(C2,"000"),TEXT(D2,"000"))
or
=A3&"-"&B3&"-"&TEXT(C3,"000")&"-"&TEXT(D3,"000")

watermt69
2019-05-16, 07:25 PM
Works like a charm, thanks a million!

watermt69
2019-05-16, 07:27 PM
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.

alansidman
2019-05-16, 08:14 PM
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.

watermt69
2019-05-16, 10:34 PM
Thanks for the clarification and assistance.