Macro help - search for text in Excel

This is a Rube Goldberg way to do this, but I've found it to be pretty effective.

Copy the entire spreadsheet and paste into a basic text editor like notepad.

Then copy the text from notepad into MS Word.

The first step is to replace all paragraph breaks with a non-numeric character but also one unlikely to appear in your data. I find that '|' is usually safe. Launch the search and replace box, enter ^p as the search string, and enter | as the replacement. Make sure you enable highlight formatting. Hit replace all. MS Word will convert all line breaks into the highlighted pipe character.

Next, you need to isolate the numbers. Open the search and replace tool. With the wildcards option enabled, enter the following as your search string: [0-9] . Set the replace option to be the 'find what text option' which is expressed as ^& . Enable formatting, and choose to make it highlighted. Then hit replace all. You will then see anything with a number now treated with a visual highlight.

Now it's time to get rid of the data you don't care about. From search and replace, you want to remove any character that is not highlighted. Choose '?' as your search string, choose 'not highlighted' from your format option, and enable wildcards. For replace, leave it blank. Hit replace all. You will then be left with only your numbers and pipes, all highlighted.

The last step is to reconvert the pipes into carriage returns. Search for '|' and replace with '^p'. You will then be left with your accounts, a carriage return reflecting each seperate record.

Copy the above, paste into notepad, then paste into a column in MS excel. Voila.

This method isn't great if your data includes numeric characters that you don't want. (numbers that aren't part of the account string)

You can write a VB function that does the above or use the macro recorder if you need to run this multiple times.

Again, definitely not elegant but works. I've found the wildcard tool in MS to be very robust, provided you are willing to get creative.