CHOOSECOLS Function + Format Data

[zero269]

Hello,

I'm wondering if it's possible to format data for any of the column data being returned using the CHOOSECOLS Function?

Right now I've got a work in progress using something cobbled together with a portion of code provided by Fluff which I found here as it relates to LAMBA and TEXTJOIN.

Although I'm currently using TEXT(Points,"0.0"),Level,TEXT(Words,"#,###") in another scenario to format the numbers, I can't figure out how to format the output for any of the given columns of data that are being joined when using the formula in my sample data below.

VBA Testing.xlsm
	A	B
1	Test Date:	11-May
2
3	Quiz | F/NF | Title | Points | Level | Words
4	122822 | F | Mysterious Island (Saddleback) | 1 | 3.5 | 6146
5	186904 | NF | Pilgrims' Voyage to America | 0.5 | 3.7 | 1918
6
7	Desired Output with formatted POINTS and WORDS:
8	122822 | F | Mysterious Island (Saddleback) | 1.0 | 3.5 | 6,146
9	186904 | NF | Pilgrims' Voyage to America | 0.5 | 3.7 | 1,918
10
11	Desired Output with a TAB to keep TITLES aligned:
12	122822 | F | Mysterious Island (Saddleback) | 1.0 | 3.5 | 6,146
13	186904 | NF | Pilgrims' Voyage to America | 0.5 | 3.7 | 1,918
Format

Cell Formulas
Range	Formula
A4:A5	A4	=BYROW(SORT(FILTER(CHOOSECOLS(t_Students,1,3,2,4,5,6), ISNUMBER(SEARCH($B$1,t_Students[Test Date])),"No Quiz"),6,-1), LAMBDA(br,TEXTJOIN(" | ",TRUE,br)))
Dynamic array formulas.

Regarding the use of TAB in a formula is likely a separate post. I've tried using CHAR(9) as the delimiter to no avail in various scenarios: TEXTJOIN, CONCAT and just using the ampersand (&).

Any help with better understanding how to handle the output of column data when using CHOOSECOLS would be greatly appreciated.

Thanks…

 

[zero269]

I get the impression that CHAR(9) (Tab) cannot be used as a delimeter when joining cells using TEXTJOIN, CONCAT, etc.

However, once I can figure out how to format the data being returned from the CHOOSECOLS array, I'm thinking the following may be my best bet as it relates to the formatting.

In the sample data below, I'm using IF to check if the value in F/NF is "F", and if so, then pad it with three spaces, else return 'NF'. This will keep the TITLEs alinged for greater readability.

IF(E3="F","F   ",E3)

I'm also able to format both POINTS and LEVEL as a RANGE using:

TEXT(G3:H3,"0.0")

Any advice on how to best isolate the data from CHOOSECOLS to format it before joining it into a single cell would be greatly appreciated...

VBA Testing.xlsm
	A	B	C	D	E	F	G	H
1	SAMPLE DATA							TEXTJOIN + FORMAT
2	Quiz	F/NF	Title	Points	Level	Words		Quiz | F/NF | Title | Points | Level | Words
3	122822	F	Mysterious Island (Saddleback)	1	3.5	6146		122822 | F | Mysterious Island (Saddleback) | 1.0 | 3.5 | 6,146
4	186904	NF	Pilgrims' Voyage to America	0.5	3.7	1918		186904 | NF | Pilgrims' Voyage to America | 0.5 | 3.7 | 1,918
Tab_Delim

Cell Formulas
Range	Formula
H2:H4	H2	=TEXTJOIN(" | ",TRUE,A2,IF(B2="F","F ",B2),C2,TEXT(D2:E2,"0.0"),TEXT(F2,"#,###"))