Excel Formula - stringing cells with Concatenate-Len in 1 cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2393055%22%20slang%3D%22en-US%22%3EExcel%20Formula%20-%20stringing%20cells%20with%20Concatenate-Len%20in%201%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2393055%22%20slang%3D%22en-US%22%3EUtilizing%20Excel%202016%20-%20I%20need%20to%20string%20together%204%20cells%20in%20a%20column.%3CBR%20%2F%3ECell%20A1%20%3DCONCATENATE(LEN(D1)%2C%E2%80%9D%3A%E2%80%9D%2CLEN(C1)%2C%E2%80%9D%3A%E2%80%9D%2CLEN(B1))%3CBR%20%2F%3ECell%20B1%3CBR%20%2F%3EXA%3CBR%20%2F%3ECell%20C1%3CBR%20%2F%3E0010%3CBR%20%2F%3ECell%20D1%3CBR%20%2F%3EHELUA662%3CBR%20%2F%3E%3CBR%20%2F%3EI%20need%204%20cells%20to%20be%20combined%20to%20read%3A%3CBR%20%2F%3EHELUA6620010XA%3CBR%20%2F%3E%3CBR%20%2F%3EThey%20have%20to%20be%20exact%20and%20cannot%20drop%20any%20zeros%20in%20front%20of%20the%20numbers.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2393055%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2393117%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20stringing%20cells%20with%20Concatenate-Len%20in%201%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2393117%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3EAre%20these%20fields%20contain%20fixed%20number%20of%20digits%20%3F%3F%3CBR%20%2F%3EI%20mean%20four%20digits(0010)%20like%20(1234)%20or%20may%20contain%20more%20%3F%3F%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2393127%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20stringing%20cells%20with%20Concatenate-Len%20in%201%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2393127%22%20slang%3D%22en-US%22%3EYou%20can%20use%20this%20formula%20to%20get%20the%20desired%20result%20in%20Cell%20A1%3CBR%20%2F%3E%3DCONCATENATE(D1%2CTEXT(C1%2C%220000%22)%2CB1)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2393162%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20stringing%20cells%20with%20Concatenate-Len%20in%201%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2393162%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1065047%22%20target%3D%22_blank%22%3E%40PGCKID%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimply%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%20%3DCONCATENATE(D1%2CC1%2CB1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Utilizing Excel 2016 - I need to string together 4 cells in a column.
Cell A1 =CONCATENATE(LEN(D1),”:”,LEN(C1),”:”,LEN(B1))
Cell B1
XA
Cell C1
0010
Cell D1
HELUA662

I need 4 cells to be combined to read:
HELUA6620010XA

They have to be exact and cannot drop any zeros in front of the numbers.

Thank you!
7 Replies
Hi,
Are these fields contain fixed number of digits ??
I mean four digits(0010) like (1234) or may contain more ???
You can use this formula to get the desired result in Cell A1
=CONCATENATE(D1,TEXT(C1,"0000"),B1)

@PGCKID 

Simply:

 =CONCATENATE(D1,C1,B1)

 

They do not have fixed digits. And I have to combine the formula already used in A1 to combine all 4 cells information somehow. Thank you!
I am sorry Detlef Lewin,
But this will not include the leading zeros..
Thats what Pamela Concepcion asked for..

@BhargavDubal 

It worked in my spreadsheet.

 

@Detlef Lewin 

The green arrow on the left top corner in the image represents Text Formatting..
In the formula I gave I have added it in case of other format...

 

For reference..Click here.

Also read the comment at the end of the page by chris.