Forum Discussion

danielS1275's avatar
danielS1275
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's avatar
    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)

     

danielS1275's avatar
danielS1275
Copper Contributor
Apr 01, 2022
I need to have formulas in case any extra data comes in. These cells do not have a set pattern to them as it can have several numbers or letters to them based on where the data comes in from.
mathetes's avatar
mathetes
Gold Contributor
Apr 01, 2022

danielS1275 You wrote as a follow-on to your original:

These cells do not have a set pattern to them as it can have several numbers or letters to them based on where the data comes in from.

 

So it's possible that my formulas, based on your original set of examples, would need to be modified to add nuances. But they should point you in the right direction. 

Resources