Forum Discussion

Kowsivenus's avatar
Kowsivenus
Copper Contributor
Jul 21, 2021
Solved

Extract number from a text after symbol "X/x”

I have the following text in a column, where I need to extract number next to second "X", in this text, it is 54.

 

40sHT + 2/20sCMD X 30sHT + 2/20sCMD 56 X 54 54" AWM/C129-DOBY

 

Some other sample text were,

21sOE X 12sFL 56 X 36 63" PLAIN - Result must be : 36

40sC X 40sC_100 X 91_63" PLAIN - Result 91

16sOE x 12sLY 84 x 48 71" 3/1 DRILL - Result 48

 

Regards,

Kowsi

excel
  • HansVogelaar's avatar
    HansVogelaar
    Jul 21, 2021

    Kowsivenus 

    Let's say the first value is in A1.

    In another cell in row 1, enter the formula

     

    =IFERROR(--TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(UPPER(A1),"X",REPT(" ",255),2),255)),"_"," ")," ",REPT(" ",255)),255)),"")

    This can be filled down.

     

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 21, 2021

    Kowsivenus 

    Let's say the first value is in A1.

    In another cell in row 1, enter the formula

     

    =IFERROR(--TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(UPPER(A1),"X",REPT(" ",255),2),255)),"_"," ")," ",REPT(" ",255)),255)),"")

    This can be filled down.

     

Resources