I don't know if this will help you in any way, but since I just created it, I know someone out there will benefit from it.
Essentially, I had an Excel spreadsheet with a column of items. I'm trying to find which cells have the text I'm looking for. When I find that text, I want a specific word so I can add up the cells with the new text. Part of the problem is that there are multiple similar words and I want to have a new word substitituted to match my needs for the next step.
I've done this in the past with the Find and Substititue commands in Excel. Part of the problem is that I need multiple rows to create the formulas needed. I got an idea about what if there was a way to have one cell at the top with the phrase I was looking for and the new word to used if I found that word. Well, it took about 10 minutes to create what I needed.
Here's a sample of the table I made.
|2||TEXT WITH WORD-B IN IT||B||Cool||WORD-||Got Text?|
|3||TEXT WITH WORD-B IN IT||B||Cool||WORD-||Got Text?|
|4||TEXT WITH WORD-A IN IT||RESULT-A||Cool||WORD-||Got Text?|
|5||WORD-C IN THIS ONE||WORD-||Got Text?|
|6||DON'T HAVE THE TEXT IN IT||Cool|
|7||TEXT WITH WORD-B IN IT||B||Cool||WORD-||Got Text?|
|8||TEXT WITH WORD-A IN IT||RESULT-A||Cool||WORD-||Got Text?|
Here's the formula you would put into cell B2
Then you can copy it to each additional row and column.
In column A is the text to search for.
At the top of Columns B through F are 3 items.
The word on the left is what we want to see as a result.
Then we have the "~" that is used to separate what's on the left and right. If you had a "~" in your Column A, you'd need to change the Find("~" at the end of the formula to a different character and change the character at the top of each row.
The word on the right is the word or phrase we're searching for in Column A.
The reason for Columns B - F is to search for multiple different words or phrases.
How could you use this? Say you had a long list of phone numbers. You want to find only the ones with a certain area code. You could have the result be the original area code, or use "1" so you can get a sum later (but you may need an extra column next to the numbers with =VALUE(D2) to convert the 1 from text to an actual number. Never mind. I just adjusted the formula to convert a number to an actual number so you can get a SUM() calculation.
Or...use it to validate good text and bad text. You can always sort your columns so you're good results are at the top or bottom.
Any questions? You can email me if you do. I'm available for Excel consulting services. Please let me know if this helped you in any way.