Forum Discussion

Brian Jarvis's avatar
Brian Jarvis
Copper Contributor
Jan 05, 2018

Find and replace? Delete some characters but leave others?

Hello, All!

 

I'm trying to do a find & replace that would turn this:

       someText(25)

into:

       25

 

The 25 could be any number value. Basically, I want to delete "someText(" and the the last parenthesis. Can I use find & replace to do this? Thanks!

Formulas & Functions
Need Help

2 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Jan 05, 2018

    Brian,

     

    If you have Excel 2013 or later then the best way to clean your data is to use the https://support.office.com/en-us/article/Video-Flash-Fill-9837ec4f-b45b-4c03-af6d-ff900c202032?ui=en-US&rs=en-US&ad=US.

     

    For example, imagine that you have some data in range A1:A21 as shown in the below screenshot,

    and you want to extract the numbers only, in cell B1 type 82,

    then step down to cell B2 and press Ctrl+E.

     

     

    If you have Excel 2010 or earlier, use this formula:

    =SUBSTITUTE(REPLACE(A1,1,SEARCH("(",A1),""),")","")

     

  • Kenneth Reyes's avatar
    Kenneth Reyes
    Copper Contributor
    Jan 05, 2018

    Hi Brian,

     

    can you try this one?

    =REPLACE(REPLACE(A1,1,FIND("(",A1),""),FIND(")",REPLACE(A1,1,FIND("(",A1),"")),100,"")

     

    Thanks,

    kenkie

Resources