+ Reply to Thread
Results 1 to 5 of 5

Search and replace Text Replace Formula

  1. #1
    Registered User
    Join Date
    11-25-2017
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    15

    Search and replace Text Replace Formula

    Hi All,

    I'm seeking help off you good people. I'm trying to find a formula that will search for the "right" most "-" and return all characters to the left of it.

    E.g.

    PI-140568- would become PI-140568
    PI-140573-17 would become PI-140573
    PI-1991-2 would become PI-1991
    PI-110869 would remain as PI-110869

    Warm Regards and thanks in advance for any help.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,743

    Re: Search and replace Text Replace Formula

    Perhaps =IFERROR(LEFT(A1,FIND("%",SUBSTITUTE(A1,"-","%",2))-1),A1)

  3. #3
    Registered User
    Join Date
    11-25-2017
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    15

    Re: Search and replace Text Replace Formula

    Brilliant Pepe, Thank you ever so much.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Search and replace Text Replace Formula

    Or this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 PI-140568 PI-140568
    2 PI-140573-17 PI-140573
    3 PI-1991-2 PI-1991
    4 PI-110869 PI-110869
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,743

    Re: Search and replace Text Replace Formula

    There's more than one way to skin a cat...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro: Search and replace: Replace using a cell reference
    By kalyan46 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2019, 07:11 PM
  2. [SOLVED] How to replace specific text search for any date search
    By katiedee1625 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2018, 11:22 AM
  3. [SOLVED] Search and replace text
    By wincross in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-10-2016, 06:44 AM
  4. [SOLVED] VBA replace text command to replace a formula with text
    By MikeSta4ord in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-04-2015, 01:08 PM
  5. Search and Replace Text with a formula and adjacent cell with a formula
    By 1p3261 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2015, 12:14 PM
  6. Replies: 2
    Last Post: 11-19-2013, 11:49 AM
  7. [SOLVED] search for text, then search for previous text, replace 1st text including number from 2nd
    By kldailey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2012, 05:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1