Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Replacing Part of Text with Another Part of Same Cell

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Replacing Part of Text with Another Part of Same Cell

    Hello!

    I'm hoping someone can help me with my current problem. I have thousands of cells that look like this:


    What I'd like to do is replace the "nomodel" text above with whatever is between (in this case, it is Unit-Model-Here).

    I've looked in other threads and have tried to adapt solutions from there, but have yet to figure out a working one. Any help would be much appreciated.

  2. #2
    New Member
    Join Date
    Feb 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replacing Part of Text with Another Part of Same Cell

    Apologies for the example format screwing up. Hopefully this appears better.

    Unit-Model-Here

  3. #3
    New Member
    Join Date
    Feb 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replacing Part of Text with Another Part of Same Cell


    Really sorry for triple posting but it appears the only way the code will properly show up is if I include an image, which is attached. Sorry again for the triple post.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,511
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Replacing Part of Text with Another Part of Same Cell

    I have assumed your values are in Column A starting on Row 1 and I put the output in Column N although if your aim is to output the corrected data over top of the original data, you can change the N1 reference in the last line of code to A1.
    Code:
    Sub ReplaceNoModelText() Dim R As Long, Data As Variant, Parts() As String Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)) For R = 1 To UBound(Data) Parts = Split(Data(R, 1), ">") Data(R, 1) = Replace(Data(R, 1), "/nomodel.", "/" & Parts(UBound(Parts) - 1) & ".") Next Range("N1").Resize(UBound(Data)) = Data End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member
    Join Date
    Feb 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replacing Part of Text with Another Part of Same Cell

    Thank you so much! It appears that it works great up until line 276, and then it includes (less than symbol, slash symbol, and an a) at the end of the replaced text. I typed out what is in parenthesis because it wasn't formating properly in the post.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,511
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Replacing Part of Text with Another Part of Same Cell

    Quote Originally Posted by Meatballsub View Post
    Thank you so much! It appears that it works great up until line 276, and then it includes (less than symbol, slash symbol, and an a) at the end of the replaced text. I typed out what is in parenthesis because it wasn't formating properly in the post.
    Then your data starting at line 276 is not structured the same as the lines of text before it. Show me what is in line 276 and I'll see if I can spot the difference for you.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replacing Part of Text with Another Part of Same Cell

    Sorry, the text between the A tags definitely varies throughout the sheet. Here is line 276 and then one from further down that is pretty generic (though still has the same issue).



  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,511
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Replacing Part of Text with Another Part of Same Cell

    Quote Originally Posted by Meatballsub View Post
    Sorry, the text between the A tags definitely varies throughout the sheet. Here is line 276 and then one from further down that is pretty generic (though still has the same issue).


    I'd rather not type those out... can you post the actual lines of text like you did in the first message? Don't worry that it does not show up correctly... I can get to the actual posted lines elsewhere.

    Also, tell me what text is supposed to be used from each of those to replace the 'nomodel' text.
    Last edited by Rick Rothstein; Feb 21st, 2018 at 02:42 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    New Member
    Join Date
    Feb 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replacing Part of Text with Another Part of Same Cell

    Absolutely, and thanks again for help.


  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,511
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Replacing Part of Text with Another Part of Same Cell

    Quote Originally Posted by Meatballsub View Post
    Absolutely, and thanks again for help.
    Hmm! I don't get the error that you reported earlier... the replacement for both worked as you indicated you wanted. However, I do note the first one will not worki when you try to use it as the text contains a colon and a colon is not allowed in a filename. Perhaps that first one should stop at the comma? But in neither case does my code produce the odd result you reported earlier. Maybe there are some odd, non-visible characters in the file that is messing things up. Can you post a copy of your workbook to DropBox so that we can download the actual file with your actual data in order to test solutions against?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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