Excel find replace

Posted on 2012-08-23
Last Modified: 2012-08-24
Hi ,

I have a column with values like below.

Home > Work / Tools > Office Work Working Business Decal Stickers 002
Home > Work / Tools > test > Office Work Working Business Decal Stickers 002

Need output like

Home > Work / Tools
Home > Work / Tools > test

I need the last value of > get removed.

Question by:magento
    LVL 24

    Accepted Solution

    See attached workbook with function "Splitit" acoded below:

    Function splitit(StartString As String, Char As String) As String
    Dim xx As Variant
    xx = Split(StartString, Char)
    splitit = Replace(StartString, xx(UBound(xx)), "")
    End Function

    Open in new window

    This should do the job.
    LVL 5

    Author Comment


    Worked exactly as needed.

     Thank you .
    LVL 18

    Expert Comment


    May be

    Hit Ctrl + H

    Find what: "> Office*" ( without quotes)

    Leave empty Replace with column

    LVL 5

    Author Comment

    Hi Kris,

    Your solution will work only on text with Office.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now