Finding words in Excel?

Question

Finding words from a list in Excel?
I have a list of 30000 names in the form johnsmith and I know if I wanted to flag all the cells with "johnsmith" I could write: =(VLOOKUP(A1,names,1,FALSE),A1... where A1 is the what I'm testing and names is the table of names that I want to key in on. But what if I wanted all names with smith? I assume I need something like: FIND(VLOOKUP(A1,test,1,FALSE),... but it won't work. *Note the names are in the form johnsmith, so with no space I have nothing to parse on, so I will need some kind of find function

Answer 1

Assume your list of names starts in cell A1. Insert a new col. B. In cell B1 type the formula:

=IF(ISNUMBER(SEARCH
("smith",A1)),"x","")

The formula is split onto two lines to display properly here. Copy and paste it to the formula bar, and backspace to eliminate the line break.

Copy and paste from B1 down col. B through the last cell of your data in col. A.

Now in col. B there will be an "x" on every row where "smith" is found within the corresponding cell in col. A. If there is no "smith", the cell in B will be blank.

Now you can sort on col. B, to get all the "smith" rows together.

I recommend using the SEARCH function rather than FIND because SEARCH is not case-sensitive, so it will find "Smith" as well as "smith."

Here's how the formula works. The SEARCH function returns the number of the first character within the text string where "smith" begins, or it returns the error #VALUE? if no "smith" is found. The ISNUMBER function returns TRUE if the SEARCH function has returned a number. The IF function looks at the ISNUMBER result; if it's true, it puts an "x" in the cell, otherwise it makes it blank (the value "")

Answer 2

get entry to, be conscious, Excel are separate purposes bought by Microsoft and characteristic to be offered break away XP. sometime you would even see computer's that were offered new and comprise the OS and MS workplace preinstalled obviously in you case MS workplace isn't put in. If cost is a element for you you should receive a loose workplace kit observed as OpenOffice, it really is a in a position kit and it ought to also import/export MS workplace files.

Answer 3

You need a query in Excel
Look at help of Excel Query

Answer 4

just highlight the column the names are in and go to edit then find and replace then type in the name you want to find in the find area and click find. Don't enter anything in the replace area.