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)

     

mathetes's avatar
mathetes
Silver Contributor
Apr 01, 2022

danielS1275 

 

This isn't the most elegant of solutions (it could be more efficient to use the new LET function to eliminate redundancy, but then you'd have to have that most recent version of Excel).

 

Here's the formula that gets the letter(s)

=IFS(RIGHT(A$1,1)="W","W",RIGHT(A$1,1)="M","M",RIGHT(A$1,2)="Mo","Mo")

 

Here's the formula that gets the number.

=IFS(RIGHT(A1,1)="W",VALUE(MID(A1,FIND("-",A1)+1,FIND("W",A1)-(FIND("-",A1)+1))),RIGHT(A1,1)="M",VALUE(MID(A1,FIND("-",A1)+1,FIND("M",A1)-(FIND("-",A1)+1))),RIGHT(A1,2)="Mo",VALUE(MID(A1,FIND("-",A1)+1,FIND("Mo",A1)-(FIND("-",A1)+1))))

 

Resources