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 la...
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))
     )
  )

Resources