You are here:

Excel/Re: HELP! MS Excel (Search, Find, Replace and Extract Multiple Text)

Advertisement


Question
Hi Gulshan,

I need to use MS Excel 2010 to Search, Find, Replace and Extract/Split matching text (case-sensitive, if at possible) found within a string of text within a cell.  I would like to use a separate worksheet that contains a library of words “Find What” and “Replace With”, whereas if the cell contains one or more matching keywords, it replaces the discovered keyword with the corresponding “Replace With” text.

Once the word is found, then remove the discovered keyword from the text in a separate cell and return the identified “Replace With” keyword in a separate cell.  Then parse each keyword using the “Replace With” keyword, which includes pre-defined Delimiters ”^”, that converts the text into separate adjacent columns.

I’ve taken the liberty of creating a sample MS Excel workbook containing sample data for your review and consideration in assisting.  Please let me know as I can forward to your attention a copy of the actual workbook.


EXAMPLE: (Upon request I can send you an actual sample spreadsheet)

SAMPLE DATA Text string found in a single cell
An accident involved a Nissan Maxima, Honda Pilot, Ford Festiva and a Pickup truck

{Please Note: Have a separate worksheet containing a list of "Find What" (column A) and a list of corresponding "Replace With" (column B) text)

Step 1: SEARCH & FIND WHAT
FIND WHAT (List of Keywords to search in a separate worksheet (Column A) entitled: "Find What")

- Nissan Maxima
- Honda Pilot
- Ford Festiva

Step 2: REPLACE WITH (List of keywords to match Find What with Replace With in a separate worksheet (column B) entitled: "Replace With")
(Note: Replace With matching text containing character Delimiters "^")

^Nissan Maxima^
^Honda Pilot^
^Ford Festiva^


Step 3: FIND & REPLACE (Note: Remove old "Find What" text with new "Replace With" keyword)
Returns the data with replaced with keywords

- An accident involved a^Nissan Maxima^,^Honda Pilot^,^Ford Festiva^and a pickup truck


Step 4: REMOVE TEXT (Note: Remove "Replace With" keyword(s) from text and returns original data omitting keywords)
- An accident involved a and a pickup truck

STEP 5: CONVERT TEXT DELIMINATED TEXT (Note: Join and remove, where multiple Delimiters are present to a single Delimiter)

- Original Deliminated Data: ^Nissan Maxima^^Honda Pilot^^Ford Festiva^
- Modified Deliminated Data: ^Nissan Maxima^Honda Pilot^Ford Festiva^


STEP 6: FINISH AND PARSE DATA
Splits text into separate columns where Delimiter "^" is found
Auto Type 1   Auto Type 2   Auto Type 3
Nissa Maxima   Honda Pilot   Ford Festiva

Sincerely,
Tired, Weary, Drained & MS Excel Confused

Answer
Hi Dean,

Find and replace can be easily handled. Even if there are multiple search and replace values, we may still be able to use a macro to do the find and replace efficiently.

However, I feel that you are doing some steps which can be avoided and you'll still achieve the end objective.

I would like to have a look at your file.

Please send me the file at gulthemacroguy@gmail.com and do include AllExperts in the subject line.

Thanks a lot,
Gulshan.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.