+ Reply to Thread
Results 1 to 17 of 17

More Versatile Find and Replace - Find and Replace Within a Cell

  1. #1
    Registered User
    Join Date
    11-25-2014
    Location
    Swindon, England
    MS-Off Ver
    2013
    Posts
    30

    More Versatile Find and Replace - Find and Replace Within a Cell

    Hi there folks,

    I'm currently looking for a solution to this issue.
    I have a spreadsheet of numbered lists within many cells.
    For example,
    Within one cell I have:

    1. On a plane
    2. On a boat
    4. In a car
    5. Many other vehicles

    These lines are separated with line breaks.

    Then in the next cell I have more numbered lines starting again from 1.

    This spreadsheet is huge and I'm looking to be able to replace any words or phrases in a selection.

    For example in the above case, I would wish to change all instances of "Many other vehicles" to read "Alternative transport", but the issue with the standard find and replace button is that it searches per cell, and not within cells. I want to be able to run a macro or use excel's functions to look through the sheet for a word or phrase within each cell and if it's present, replace it with another pre defined string of characters.

    I'm really looking to be able to understand how the solution was developed so a few pointers and an explanation would be great.

    After this problem has been sorted, I eventually want to be able to look through a spreadsheet and instead of replacing specific phrases, I need
    Last edited by KristofferA; 12-02-2014 at 11:37 AM.

  2. #2
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    One easy fix I recommend is you create a table of the translation and reference it via vlookup or index/match.

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    You can use Find/Replace for the contents of a cell.

    What did you try?
    If posting code please use code tags, see here.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    Find/Replace will do partial text if you have the "Match entire cell contents" unchecked.
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    11-25-2014
    Location
    Swindon, England
    MS-Off Ver
    2013
    Posts
    30

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    The "match entire cell contents" button doesn't solve the problem.

    :S

    Find and replace isn't working at all. It's just saying there are no matches (presumably as there are no cells which include exactly what I wish to replace).

    Maybe I haven't explained the issue properly.

    Imagine you have lots of lists of books in different cells.

    Cell A1:
    1. Hairry Porter <line break>
    2. LOTR <line break>
    3. Game of Thrones

    Cell A2:
    1. Hairry Porter <line break>
    2. LOTR <line break>
    3. 50 Shades

    Cell A3:
    1. Hairry Porter <line break>
    2. LOTR <line break>
    3. Game of Thrones


    Now imagine I realise I've spelt "Harry Potter" incorrectly and want to look through my whole sheet and replace all instances of "Hairry Porter" with "Harry Potter"
    Last edited by KristofferA; 11-25-2014 at 12:11 PM.

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    Works for me.
    See atached. A1:A3 have been processed as described.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    You should be able to highlight the entire column, find Hairry and replace all with Harry.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    Both examples work for me too.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    Here is one thing that is a bit strange but seems to work.

    Click on Find and Select, click on FIND and enter the value that you want to find. Click on FIND NEXT or FIND ALL then click on REPLACE tab and click on REPLACE or REPLACE ALL.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  10. #10
    Registered User
    Join Date
    11-25-2014
    Location
    Swindon, England
    MS-Off Ver
    2013
    Posts
    30

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    Right okay I see the issue I'm having.
    1) Match entire cell contents was ticked
    2) To test whether it would work I was trying to replace with "@@!!@@" as it's easily visible on my spreadsheet when browsing (just as a test) and excel was recognising this as a function of some sort giving me this error: Capture.PNG

    No worries though thanks for all the help and have a good day !!

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    The first example would not work at all for me with REPLACE. Here is one thing that is a bit strange but seems to work.

    Click on Find and Select, click on FIND and enter the value that you want to find. Click on FIND NEXT or FIND ALL then click on REPLACE tab and click on REPLACE or REPLACE ALL.

    The other examples worked just fine as they should without going through the above process.

  12. #12
    Registered User
    Join Date
    11-25-2014
    Location
    Swindon, England
    MS-Off Ver
    2013
    Posts
    30

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    Hi there folks,
    Just looking for a bit more guidance on the above issue.
    I've got the find and replace to work okay if we're just replacing words, however how would I be able to find and replace "Hairy Porter" with the same word in red text.

    So, same scenario as original post, just instead of replacing "Hairry Porter" with "Harry Potter" we would be replacing "Hairry Porter" with "Hairry Porter".

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    When you use the Find and Select, Replace, click on OPTIONS. A dialogue box will open with more fields than before. Enter the text in the Find What field and enter what you want to replace it with in the Replace With field...even if it is the same text. Click on the FORMAT button and select FORMAT. When the Replace Format dialogue opens click on Font and choose the colour that you want and any other font changes then click OK. Click Replace All.

  14. #14
    Registered User
    Join Date
    11-25-2014
    Location
    Swindon, England
    MS-Off Ver
    2013
    Posts
    30

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    This seems to replace the entire contents of the cell with the format chosen. Say I have a whole sentence in a cell but only want to change the font of one word within that cell, how do I do that?

  15. #15
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    You are correct. This might be able to be done with VBA but not by the Find and Select it seems. Is there any chance that you could copy the text into Word and do the Find and Select etc from there. Word's ability in this area is far superior to Excel's?

  16. #16
    Registered User
    Join Date
    11-25-2014
    Location
    Swindon, England
    MS-Off Ver
    2013
    Posts
    30

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    Ah ordinarily that would be fine but the sheer size of my working file is so great, it would be impractical to dismantle it all, edit it, and return it.
    Thanks a lot for the help though

  17. #17
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: More Versatile Find and Replace - Find and Replace Within a Cell

    If what you want to change is in one column, copy the column paste into Word using the right click menu and choose Keep Source Formatting. Make the format changes and copy back to Excel and paste using the right click option of Keep Source Formatting.

    Word can handle a very large amount of data.

+ 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. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  2. Find and replace but replace value of cell 3 columns along
    By batchjb69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2014, 10:57 AM
  3. find in excel replace in word: find/replace text in text boxes and headers
    By dean.rogers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2012, 12:40 PM
  4. Find, Replace with and then replace adjacent cell
    By Craig2097 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2009, 12:42 PM
  5. [SOLVED] find and replace - replace data in rows to separated by commas
    By msdker in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2006, 08:10 PM

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