SOLVED

Excel Extract Using Find and Replace Function

Copper Contributor

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

3 Replies
best response confirmed by Hans Vogelaar (MVP)
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),""),")","")

 

 

Harun24HR_0-1661135317206.png

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

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

 

 

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

@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))
     )
  )
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
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),""),")","")

 

 

Harun24HR_0-1661135317206.png

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

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

 

 

View solution in original post