Excel Extract Using Find and Replace Function

[Megan1004]

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

[#2 · Accepted solution]

@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:"),")")

[#3]

THANK YOU, THANK YOU, AND THANK YOU!!!!

[#4]

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