Forum Discussion
Solved
Returning Text from a data cell
Hi all, I have been trying to find a set of formulas that will extract certain numbers or text from data cells and returns them to a separate column but I have had no luck so far. This is an exce...
Here are formulas that will work in older versions of Excel too.
With a value such as US351-4W in A1:
=--LEFT(MID(A1, FIND("-", A1)+1, 100), MATCH(FALSE, ISNUMBER(--MID(MID(A1, FIND("-", A1)+1, 100), ROW(INDIRECT("1:"&LEN(MID(A1, FIND("-", A1)+1, 100)))), 1)), 0)-1)
and
=MID(MID(A1, FIND("-", A1)+1, 100),MATCH(FALSE, ISNUMBER(--MID(MID(A1, FIND("-", A1)+1, 100), ROW(INDIRECT("1:"&LEN(MID(A1, FIND("-", A1)+1, 100)))), 1)), 0), 100)
This really need regular expressions but, failing that,
= MAP(data,
LAMBDA(d,
LET(
duration, TEXTAFTER(d, "-"),
char, MID(duration, SEQUENCE(LEN(duration)), 1),
num, IFERROR(--char, ""),
--CONCAT(num)
)
)
)works in the latest versions of 365.