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

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.