TEXTJOIN/ CONCATENATE with numbering and formatting

[Shark88]

I’m trying to combine a series of cells but want it in a list format, so for example if the cells are:
A1 Apple
A2 01/01/2024
A3 Banana
A4 02/01/2024
A5
A6

I want it to read:

1. Apple 01/01/2024
2. Banana 02/01/2024

I’m currently using

TEXTJOIN(“”,TRUE, “1.”,A1,TEXT(A2, “mm/dd/yyyy”),CHAR(10), “2.”,A3,TEXT(A4, mm/dd/yyyy”),CHAR(10), “3.”, A5, TEXT(A6, mm/dd/yyy”))

The problem is that each column has varying numbers of fields to combine so I want it to essentially stop adding the numbering and empty date showing up as 01/00/1900.

If I remove the text for the date formatting, regardless of how the information is stored it keeps trying to return it as a number and I have not found another way of fixing that. I can’t seem to find another way to add a number in front to create steps in a list either, but I end up having the following returned:

1. Apple 01/01/2024
2. Banana 02/01/2024
3. 01/00/1900

 

[JamesCanale]

Not at all sure that I have what you want here,

MrExcelPlayground23.xlsx
	A	B	C
1	Apple		1. Apple 01/01/2024
2	1/1/2024		2. Banana 02/01/2024
3	Banana
4	2/1/2024
5
6
7
8
9
10
Sheet10

Cell Formulas
Range	Formula
C1:C2	C1	=LET(a,A1:A10,b,INDEX(a,SEQUENCE(ROWS(a)/2,2)),c,FILTER(b,CHOOSECOLS(b,2)>0),d,ROWS(c),e,HSTACK(SEQUENCE(d),c),f,BYROW(e,LAMBDA(a,INDEX(a,,1)&". "&INDEX(a,,2)&" "&TEXT(INDEX(a,,3),"mm/dd/yyy"))),f)
Dynamic array formulas.

 

[Cubist]

One option:

Book1
	A	B
1	Apple	1. Apple 01/01/2024 2. Banana 02/01/2024 3. Cake 03/01/2024 4. Danish 04/01/2024
2	1/1/24
3	Banana
4	2/1/24
5	Cake
6	3/1/24
7	Danish
8	4/1/24
9
10
11
12
13
14
Sheet5

Cell Formulas
Range	Formula
B1	B1	=LET( d,TOCOL(A1:A14,1), x,DROP(d,-1) & " " & TEXT(DROP(d,1),"mm/dd/yyyy"),r,ROWS(d), TEXTJOIN(CHAR(10),,SEQUENCE(r/2) & ". " & FILTER(x,MOD(SEQUENCE(r-1),2)=1)))

 

[Cubist]

A little shorter.

=LET(
x,WRAPROWS(TOCOL(A1:A14,1),2),
TEXTJOIN(CHAR(10),,SEQUENCE(ROWS(x)) &". "& INDEX(x,,1) & " " & TEXT(INDEX(x,,2),"mm/dd/yyyy")))

 

[Kaper]

Standard (no LET so shall work also with Excel 2019 and 2021) formula:

=TEXTJOIN("",TRUE,IF(A1:A16="","",IF(ISODD(ROW(A1:A16)),(ROW(A1:A16)+1)/2 & ". " & A1:A16,TEXT(A1:A16," mm/dd/yyyy") & CHAR(10))))

Remember to set cell formatting as wrapping text and wide enough to fit description and date.

note empty first argument ("") and space in front of date formatting string "_mm/dd/yyyy"

 

[Rick Rothstein]

A little shorter.

=LET(
x,WRAPROWS(TOCOL(A1:A14,1),2),
TEXTJOIN(CHAR(10),,SEQUENCE(ROWS(x)) &". "& INDEX(x,,1) & " " & TEXT(INDEX(x,,2),"mm/dd/yyyy")))

Here is a little bit shorter formula that will also work...

=TEXTJOIN(HSTACK(" "," ",CHAR(10)),,HSTACK(SEQUENCE(ROWS(A1:A8)/2),TEXT(WRAPROWS(A1:A8,2),"dd/mm/e")))

 

[Cubist]

Here is a little bit shorter formula that will also work...

=TEXTJOIN(HSTACK(" "," ",CHAR(10)),,HSTACK(SEQUENCE(ROWS(A1:A8)/2),TEXT(WRAPROWS(A1:A8,2),"dd/mm/e")))

Very interesting array behavior. I didn’t know TEXTJOIN behave like that.

 

[Rick Rothstein]

Very interesting array behavior. I didn’t know TEXTJOIN behave like that.

All the TEXTxxx functions can take an array of values as their delimiter. TEXTJOIN distributes them cyclically whereas the others treat all the arrayed delimiters as if they were the same delimiter. Here is the text from a mini article that I posted on LinkedIn about this...

Okay, I just discovered something about the TEXTJOIN function through experimentation that I was not aware of before... you can specify multiple delimiters and they will be applied in the order listed! Put some text in each of the cells of the range A1:A4 and then put this formula in another cell and look at how the delimiters are distributed between the text from the cells...

=TEXTJOIN({"-","/","="},,A1:A4)

Neat, right? Even neater is if there is more text than delimiters to separate them, the delimiters are applied cyclically. For example, put text in each of the cells of the range A1:A6 and then put this formula in another cell and look as how the two delimiters are distributed through the six text strings...

=TEXTJOIN({"-","/"},,A1:A6)

Really, really neat, right?

 

[Phuoc]

Or try this:

Book1
	A	B
1	Apple	1. Apple 01/01/2024 2. Banana 02/01/2024 3. Cake 03/01/2024 4. Danish 04/01/2024 5. Banana 02/01/2024 6. Cake 03/01/2024
2	1/1/2024
3	Banana
4	1/2/2024
5	Cake
6	1/3/2024
7	Danish
8	1/4/2024
9	Banana
10	1/2/2024
11	Cake
12	1/3/2024
13
14
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN(CHAR(10),,IF(ISNUMBER(A2:A14),SEQUENCE(ROWS(A2:A14))/2+0.5& ". " & A1:A13& TEXT(A2:A14," mm/dd/yyyy"),""))

 

[Cubist]

All the TEXTxxx functions can take an array of values as their delimiter. TEXTJOIN distributes them cyclically whereas the others treat all the arrayed delimiters as if they were the same delimiter. Here is the text from a mini article that I posted on LinkedIn about this...

Okay, I just discovered something about the TEXTJOIN function through experimentation that I was not aware of before... you can specify multiple delimiters and they will be applied in the order listed! Put some text in each of the cells of the range A1:A4 and then put this formula in another cell and look at how the delimiters are distributed between the text from the cells...

=TEXTJOIN({"-","/","="},,A1:A4)

Neat, right? Even neater is if there is more text than delimiters to separate them, the delimiters are applied cyclically. For example, put text in each of the cells of the range A1:A6 and then put this formula in another cell and look as how the two delimiters are distributed through the six text strings...

=TEXTJOIN({"-","/"},,A1:A6)

Really, really neat, right?

Neat indeed. I've played around a bit and it seems like the default behavior is left to right then top down. To join top down then left right I've added TRANPOSE.
I've also noticed the format mm/dd/e what does the 'e' mean?