Forum Discussion

Copper Contributor

Apr 01, 2022

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...

excel

Formulas and Functions

HansVogelaar
Apr 03, 2022
danielS1275

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)

HansVogelaar

MVP

Apr 03, 2022

danielS1275

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)