In MS Excel, how can I search for a string in one column...?

Question

and where it finds it, add a string to another column? For example, find the word 'marriages' in one column and add the word 'marriage' to another column. Or find the word 'cemetery' in one column and add the word 'death' in another column. Another might search for 'statistics' in one column and add 'clerk' to another column. (In some cases what is searched for may be the same value to be added to another column, but in other cases, it may be a totally different word.)

I'm working with file(s) that have 5000 - 50,000 rows.

Thanks

Like 'Search & Replace", but instead of replacing, it is 'Search & Add" (to another cell in the same row)

Answer 1

Assuming your data is in column A and you wish to notate in the appropriate row in column B, this macro will accomplish your goal. It is not case sensitive and will locate Statistics, Statistics, or statistics and notate correctly.



If you wish to use different columns, modify the macro before copying:



In Line 3 replace the 'A' in A1 and the 'A' in Range("A" with the column letter your data is in.



If you do not want to notate in the very next column to the right of your data, change the '1' in "ActiveCell.Offset(0, 1).Value" to the number of columns you wish to offset for notation entry.



Open your workbook



Copy this macro, modified if necessary, to the clipboard:



Sub Custom_Find_and_Notate()

Dim rng As Range

Set rng = Range("A1:" & Range("A" & _

ActiveSheet.Rows.Count). End(xlUp).Address)

findthis = InputBox("Please enter your search criteria.", _

"Key Word")

replwith = InputBox("Please enter the text to notate.", _

"Replace With")

For Each cell In rng

cell.Select

If UCase(ActiveCell.Value) = UCase(findthis) Then

ActiveCell.Offset(0, 1).Value = replwith

End If

Next

Range("A1").Select

End Sub





Press ALT + F11



Insert > Module



Paste the macro into the module space to the right.



Close back to Excel.



Go to Tools > Macro > Macros



Highlight this macro, if it is not already highlighted.



Click 'Options...' (bottom right)



Select a letter to be used as a keyboard shortcut.



Close back to Excel.



Press CTRL + your shortcut letter to run the macro.

Answer 2

Here is a simple solution that involves Excel functions:

Let me assume you are having column 1 (The one that you want to search in) is Column A starting from A5

And the one that you have the values you want to add is in Column B starting from B5



Now, in Column C starting from C5, paste this

=IF( SEARCH( "marriages", A5,1)>0, B5 & " - marriage", B5)

Then paste it down to the end of your 50,000 rows



Now if you want the cemetery one do the same but starting from D5 like this

=IF( SEARCH( "cemetery", A5,1)>0, B5 & " - death", B5)



I tried to simplify it as much as I can, If I knew what you have on your file, I would do it much more efficient



VBAXLMan is here to feed your Excel needs

Answer 3

MMmm, it incredibly is extremely common question. So permit's attempt anticipate you have the values in column B, cellular B1, variety this function in C1 cellular =if( seek("lifestyle", B1)>0, B1, "") and reproduction and paste this function into the cells below, it incredibly is answer One I do have extra strategies, yet I might desire to nicely known in case you're with me right here or no longer, or are you able to do it, or choose particular information, so touch me for extra... study my profile, i'm the XLMan