troubleshooting Question

Dynamic Array (DA) Make the 4th item output as a value not text?

Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelMicrosoft Office
7 Comments2 Solutions13 ViewsLast Modified:
LET(RowCount,ROUNDDOWN((LEN(A1)-LEN(SUBSTITUTE(A1,",","")))/4+1,0),TokenIndex,SEQUENCE(RowCount,4,1),Start,FIND("|",SUBSTITUTE(","&A1,",","|",TokenIndex)),Length,FIND("|",SUBSTITUTE(A1&",,,,",",","|",TokenIndex))-Start,IFERROR(MID(A1,Start,Length),""))
The output looks like this (headings are manual) Col D is text and reads as zero:



The active formula is this bit at the end: IFERROR(MID(A1,Start,Length),"")
but doing this: Value(IFERROR(MID(A1,Start,Length),""))
turns the preceding three results in to blanks, like wise various Text() formats.
I also tried an IF to only do the Value thing on the 4th item, but no joy, though that may be a way I have missed.

This is the csv data for cell A1: 
Customer2,SLC,01PG,-0021600.00,Customer2,01CQ,01PG,00021600.00,Customer2,SLC,02PG,-0021600.00,Customer2,SLC,03PG,-0024840.00,Customer2,02CQ,02PG,00021600.00,Customer2,02CQ,03PG,00024840.00,ABCD ltd,PLC,01AB,00002400.00,ABCD ltd,03CQ,01AB,-0002000.00,ABCD ltd,03CQ,01AB,-0000400.00,ABCD ltd,PLC,02AB,00002400.00,ABCD ltd,04CQ,02AB,-0002400.00,ABCD ltd,PLC,03AB,00002760.00,ABCD ltd,05CQ,03AB,-0002760.00,Customer2,SLC,04PG,-0021600.00,ABCD ltd,PLC,04AB,00002400.00

or use the file attached:
DA-ValueEE.xlsx

Anthony
ASKER CERTIFIED SOLUTION
NorieSenior Associate
Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros