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 excerpt from the data I am using:

US351-4W
GER195-6Mo
GER529-2Mo
AUS301-7Mo
GER60-3W
ENG102-8Mo
ENG342-10Mo
GER442-10M
Japan17-8W
Canada559-11Mo
ARG389-11Mo
Canada121-12M

 

I need to be able to extract the numbers to the right of the dash in its own column (Ex: Japan17-8W I need to display just the number 8), and I also need to be able to display the letters at the end of the data in its own column ( Ex: ARG389-11Mo I need to display just the Mo). These two formulas need to work regardless of the length of the number or how long the series of letters at the end are. I am stuck and could really use some help! Many Thanks

 

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)

     

10 Replies

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

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 03, 2022

    danielS1275 

    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.

  • 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))))

     

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Apr 01, 2022

      I agree with Detlef_Lewin . Flash Fill is the best solution here.  A lengthy formula can be written but Flash Fill can be summoned with the press of a button at will.

       

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

Resources