Forum Discussion

Megan1004's avatar
Megan1004
Copper Contributor
Aug 22, 2022
Solved

Excel Extract Using Find and Replace Function

I'm working on a spreadsheet that has a string of text:

ITRM MODEL(INPUT:1404)(LOAD:210.555)
ITRM MODEL(INPUT:4012)(LOAD:1315.39)
ITRM MODEL(INPUT:0)(LOAD:0)

 

I need to extract the last numbers after the closing parenthesis at the end.  I used the replace formula to blank out all the text as noted:

=REPLACE(AB550,1,FIND(":",AB550,18),"") which gives me the following results:

210.555)
1315.39)
0)

Now, I'm stuck since I'm not sure what formula to use to eliminate the closing parenthesis at the end.

 

Any assistance is appreciated!!

 

Megan

excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Aug 22, 2022

    Megan1004 FILTERXML() would be best practice in this case. Use below formula to extract last node after colon. Try-

     

     

    =FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,":","</s><s>"),")","")&"</s></t>","//s[last()]")

     

     

    And if you are interested go with REPLACE() then use SUBSTITUTE() function to remove last bracket.

     

     

    =SUBSTITUTE(REPLACE(A1,1,FIND(":",A1,18),""),")","")

     

     

    And if you are an use of Microsoft 365 insiders or Current Preview channel then can try TEXTAFTER() function.

    =TEXTBEFORE(TEXTAFTER(A1,"LOAD:"),")")

     

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 22, 2022

    Megan1004 

    With traditional Excel one could have

    = VALUE(MID(string, FIND("LOAD:",string)+5,LEN(string)-FIND("LOAD:",string)-5))

    With 365, that could be tidied up to read

    = LET(
      start, FIND("LOAD:",string)+5,
      load,  MID(string, start, LEN(string)- start),
      VALUE(load)
  )

    Then again, one could go wild and develop a Lambda function using the latest release

    = ValueBetweenλ(string, "LOAD:", ")")

    where

    ValueBetweenλ
= LAMBDA(string, start, end,
     LET(
        load, TEXTAFTER(string, start),
        VALUE(TEXTBEFORE(load, end))
     )
  )
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Aug 22, 2022

    Megan1004 FILTERXML() would be best practice in this case. Use below formula to extract last node after colon. Try-

     

     

    =FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,":","</s><s>"),")","")&"</s></t>","//s[last()]")

     

     

    And if you are interested go with REPLACE() then use SUBSTITUTE() function to remove last bracket.

     

     

    =SUBSTITUTE(REPLACE(A1,1,FIND(":",A1,18),""),")","")

     

     

    And if you are an use of Microsoft 365 insiders or Current Preview channel then can try TEXTAFTER() function.

    =TEXTBEFORE(TEXTAFTER(A1,"LOAD:"),")")

     

     

Resources