+ Reply to Thread
Results 1 to 10 of 10

Excel Replace/Substitute Problem

  1. #1
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    Excel Replace/Substitute Problem

    Hi Everyone!
    I have some content in excel cell. I want to find a certain string and replace left part of that string from that cell including that string. If you see the snapshot then you will easily understand the problem.

    I have also attached the sample data in excel. Let me know if that is something we can achieve.

    Thanks in advance.

    excel forum Snapshot_2.PNG
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,791

    Re: Excel Replace/Substitute Problem

    You can do this with a formula but it has to be converted to an .xlsx file to have formulas. The idea is to use FIND to locate the snippet, and then get the MID substring starting after that snippet.

    Please note that in your first column, the text does not exactly match what is in the second column. The second column has extra spaces between all the words so it won't match. I have fixed the first column in the attached example so it will work. Also the second column has line breaks that are not in the first column. I have also added those so it works.

    On row 3, your example Result has text that does not exist in the original content.

    BTW I agree about Conti. I have one of his recordings and one of his instructional videos and it's just like he's showing off how great he is instead of teaching you anything. And "Death by Chops" is just a ridiculous tune--his time has hiccups. He is a good player, but not an artist.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    Re: Excel Replace/Substitute Problem

    Hi, I have pasted some data in your provided sheet but it the formula doesn't work. May be I am doing some mistake. Please me know about your thoughts.
    I have attached the screenshot.

    Thanks and Regards,
    Attached Images Attached Images
    Last edited by anonymous321; 12-20-2020 at 10:09 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,093

    Re: Excel Replace/Substitute Problem

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,791

    Re: Excel Replace/Substitute Problem

    Quote Originally Posted by anonymous321 View Post
    Hi, I have pasted some data in your provided sheet but it the formula doesn't work. May be I am doing some mistake. Please me know about your thoughts.
    Attachment 709607
    I can see you tried to attach a file but it is invalid. To attach a file:
    1. Under the text box where you type your reply click the Go Advanced button.
    2. On the next screen scroll down and under the Attachments section click the Manage Attachments link, which will show a pop-up window.
    3. Click the Choose File button to select a file to attached.
    4. Click the Upload button to upload the file.
    5. Then click the Close This Window button. Your file is now attached to your post.

  6. #6
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    Re: Excel Replace/Substitute Problem

    I have updated my previous reply with attachment. Please have a look now.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,086

    Re: Excel Replace/Substitute Problem

    There are double space in the sentense
    Please try this at D2 to keep double space
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or at E2 to remove double space
    =MID(TRIM(B2),SEARCH(A2,TRIM(B2))+LEN(A2),6^6)
    Attached Files Attached Files

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,791

    Re: Excel Replace/Substitute Problem

    Quote Originally Posted by anonymous321 View Post
    Hi, I have pasted some data in your provided sheet but it the formula doesn't work. May be I am doing some mistake.
    As I explained when I provided the solution, the text in column A does not match the text in column B. The text in column B has extra spaces between the words.

  9. #9
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    Re: Excel Replace/Substitute Problem

    Hi, the formula works on most of the part of data. If you look the data there are some errors in the data. Please relook the excel sheet you provided. And thanks for your effort.

  10. #10
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    Re: Excel Replace/Substitute Problem

    Quote Originally Posted by Bo_Ry View Post
    There are double space in the sentense
    Please try this at D2 to keep double space
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or at E2 to remove double space
    =MID(TRIM(B2),SEARCH(A2,TRIM(B2))+LEN(A2),6^6)
    Hi, the formula works on most of the part of data. If you look the data there are some errors in the data. Please relook the excel sheet you provided. And thanks for your effort.

+ 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. [SOLVED] Problem converting text to Excel date using LEFT and SUBSTITUTE
    By jrtaylor in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-29-2020, 05:23 PM
  2. Replace/substitute after this formula
    By bweil in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-06-2019, 01:15 PM
  3. Replace/Substitute Function
    By tushararora in forum Excel General
    Replies: 3
    Last Post: 09-10-2015, 01:14 AM
  4. Problem with multiple SUBSTITUTE in excel
    By noly123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-09-2015, 08:34 PM
  5. [SOLVED] substitute/replace until
    By JanRaven in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-02-2012, 12:03 PM
  6. Replace or substitute formula maybe? Not sure what one to use?
    By essexpoker in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2008, 11:25 AM
  7. [SOLVED] Substitute/Replace
    By Viktor Ygdorff in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-17-2006, 07:40 AM

Tags for this Thread

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