Applying a Formula to one column in a returned array

Copper Contributor

I have a TEXTSPLIT function returning an array of data for me, but one of these pieces of data is a date in format. I can easily make a formula to correct that and put it in an excel recognisable format, but I'd like to do this within the array formula if possible (trying to reduce the amount of unnecessary data in this already bloated spreadsheet).


Sorry if this is really simple and covered by Array Formulas, but I'm not at all familiar with them and can't see any way of selectively applying a formula to one item in an array whilst leaving the others un-touched, or is this impossible?


EDIT: For context, the Data in question looks like this



|02.2023 |50041885 |13195.123 |LID :MACHINING |DRILL & TAP |SPL04 | 1.749 H |0030 |12.12.2022|15.12.2022|CRTD| 2 EA |2232600 | 000001|2232600 - Marine Mot|Marine Motors & Pump| 1.000 H | 1.000 H |


TEXTSPLIT with a "|" delimiter and TRIM to remove any extra spaces.


Ideally want to convert the two dates (in this case 12.12.2022 and 15.12.2022) into DD/MM/YYYY format, which is easy enough to do with a formula, just unsure how to do that without having an extra column to handle that conversion.

1 Reply
It is hard to know without more information like what does all the other data look like? But let's say all the other data is proper dates then maybe:
=LET(origFormula, TEXTSPLIT(...), IF( ISNUMBER(origFormula), origFormula, SUBSTITUTE(origFormula, ".", "/")))