asked on Dynamic Array (DA) Make the 4th item output as a value not text?
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 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
Microsoft ExcelMicrosoft Office
Last Comment
Anthony Mellor
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
If the other columns must not be converted then only Norie's solution can do it.
If it does not matter then mine is better.
If it does not matter then mine is better.
ASKER
The first three columns are required text, the 4th being the numeric value.
Why?
Looks like
Norie's is specific to the 4th value
Yours is robust against wider errors?
Must admit I'm not sure what you mean about
"If the other columns must not be converted"
Do you mean the first three columns must not be converted to values? That is true, but yours works too.
Regret I am not exactly qualified to adjudicate... hence my question.
Anthony
If it does not matter then mine is better.
Why?
Looks like
Norie's is specific to the 4th value
Yours is robust against wider errors?
Must admit I'm not sure what you mean about
"If the other columns must not be converted"
Do you mean the first three columns must not be converted to values? That is true, but yours works too.
Regret I am not exactly qualified to adjudicate... hence my question.
Anthony
Maybe it would be a phone number with leading zeros. My formula would chop of the leading zeros.
ASKER
ok thanks saqib, that will help Me analyse both to see what I learn
Anthoy
Anthoy
I remain humbled as ever.
Anthony