How to select cells with specific text in Excel?
The Microsoft Excel FIND function returns the location of a substring in a string. The search is case-sensitive. The FIND function is a built-in function in Excel that is categorized as a String/Text Function.It can be used as a worksheet function (WS) in Excel. Type in the Range name as Text_Align; Type this formula in =GET.CELL(8,OFFSET(ACTIVE.CELL(),0,-1)) Click OK; Now any cell that you type in =Text_Align, it will return the cell that is One cell to the left of the cell you enter the formula in, You can modify the above formula to refer to any reference you need.
Supposing you need to quickly go to or select cells containing specific text from a huge worksheet, how can you quickly select cells with specific text in Excel? You can quickly select cells containing specific text as follows in Excel.
Using Kutools for Excel to select cells containing specific text
Compare two ranges and select and highlight the same or different values in Excel |
In Excel, if there are two tables in a sheet or two sheets needed to be compared, generally, you will compare one row by one manually. But if you have Kutools for Excel's Select Same & Different Cells utility, you can quickly find the same rows or different rows, and, also, you can highlight them with background color or font color as you need. Free trial for full-featured30-days! |
Kutools for Excel: with more than300 handy Excel add-ins, free to try with no limitation in30 days. |
Using Find function to select cells containing specific text
Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10! |
You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free30-day trial of Office Tab! |
For example, I have a worksheet containing data of students, now I want to select all cells containing specific student name. Using Find function in Excel to select cell containing specific text as follows:
1. Click Home > Find & Select > Find, and a Find and Replace dialog box will pop out. Input the text that you need in the Find what dropdown list. See screenshot:
2. Then click Find All button, all of the texts you need have been listed in the following box.
3. And then press Ctrl + A to select all of the values in the box. Click Close button, all of the text that you need have been selected in the range. Seen screenshots:
With Find function, you just only can select the cells, if you would like to select the entire rows with specific text, the method will not work.
Using Kutools for Excel to select cells containing specific text
With Select Specific Cells of Kutools for Excel, you can select both the cells and the entire rows with the specific values in a worksheet.
with more than 300 handy functions, makes your jobs more easier. |
After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)
1. Select the range that you want to find the specific text.
2. Click Kutools > Select Tools > Select Specific Cells, see screenshot:
3. In the Select Specific Cells dialog box, specify the selection type that you need, and choose Contains from the Specific type dropdown list, then input the value that you want to select. See screenshot:
Tip: If you want to match entire cell contents, select Equal from the Specific type drop-down list.
Tip: If you want to match entire cell contents, select Equal from the Specific type drop-down list.
4. Then click OK or Apply. A dialog pops out to remind the number of selected cells, close it, and all of the specified text will be selected.
Notes:
1. From the Specific type dropdown list, you can choose any other criteria that meet your need.
2. With this tool, you can select cells, entire rows or entire columns that you need. If you check Entire row, the result is as shown as below:
3. With this tool, you also can select two specific texts at the same time. Check Or indicate to select cells which contains oneof the two specific texts, check And means to select cells both contains the two specific texts.
Demo: Select specific cells
Kutools for Excel: 300 + functions you must have in Excel, 60-day free trial from here. |
You may intrested in this:
quickly select duplicate or unique values in an Excel range. |
In Excel sheet, if you have a range which includes some duplicate rows, you may need to select them or outstanding them, but how can quickly solve this job?If you have Kutools for Excel, you can use the Select Duplicate &Unique Cells utility to quickly select the duplicate ones or unique values in the range, or fill background and font color for the duplicates and unique values. Click for30 days free trial! |
Kutools for Excel: with more than300 handy Excel add-ins, free to try with no limitation in30 days. |
Related article:
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than300 powerful features. Supports Office/Excel2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by50%, and reduces hundreds of mouse clicks for you every day!
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
- when i have choose 1 then particular selectable word will appear.. how to make this possible?
what the formula for using this type of case??
pls ans for that - To post as a guest, your comment is unpublished.Thank you, it is very helpful :-)
I am doing genetic genealogical research, and I have long lists of surnames from possible relatives, which I am comparing manually in Excel, in order to find names that people share. Though this approach does work, it is time-consuming and error-prone. I am looking for a way to select two cells filled with surnames in Excel, and compare them to find out which names show up in both cells. For example, one cell includes the text 'Smith, Jones, Wilson..(plus another 100 names)' and the other cell includes the text 'Baxter, Lewis, Wilson..(plus another 100 names)'. I need a tool that I can activate which will spit out 'Wilson' as the term that both cells have in common. I've seen code-oriented tools that compare every character difference between two files, which is not what I'm looking for. I've seen a bunch of tools that compare text files, but since I have thousands of entries in an excel file I'd really like to do the comparison in Excel; or at least just paste the text from excel into another tool. This Compare Suite website is the closest thing I've found to what I'm looking for: http://www.comparesuite.com/online/try_for_free.php. Does anyone know if something like this exists in Excel, or in some other form (Mac or online)? Thanks!
Is there a reason one cell contains many surnames?
If they are comma seperated, you can select the text, paste it into a .txt file, then re-open with excel (do a file|open, filetype .txt), and deliniate on the commas. Then you have the information in seperate cells.
If you have two columns of names (from the two original cells), then you can easily see which names in the second column show up in the first, by creating a third column, which checks to see if stuff in the second column has a match in the first
Then column C will tell you which of the ones in B have a match in A. Spelling must be identical, and I think capitalization too.
posted by defcom1 at 6:13 PM on December 6, 2010 [2 favorites]
If they are comma seperated, you can select the text, paste it into a .txt file, then re-open with excel (do a file|open, filetype .txt), and deliniate on the commas. Then you have the information in seperate cells.
If you have two columns of names (from the two original cells), then you can easily see which names in the second column show up in the first, by creating a third column, which checks to see if stuff in the second column has a match in the first
A B C
1 2 ..formula..
3 5 ..formula..
4 6 ..formula..
5 7 ..formula..
where ..formula.. is
=IF(ISNA(MATCH(B1,$A$1:$A$4,0)),'no match','match').
Then column C will tell you which of the ones in B have a match in A. Spelling must be identical, and I think capitalization too.
posted by defcom1 at 6:13 PM on December 6, 2010 [2 favorites]
You don't need to do defcom's extra step of exporting to a .txt file and then back to excel -- you can just do text to columns within excel (do a help search on how to do this, since you don't say what version of Excel you're using) and delineate on whatever separates the names (commas, spaces, what have you)
You might then have to do a transpose to turn rows into columns, depending on how it's set up?
But other than that, he's spot-on -- get these things out of one cell.
posted by brainmouse at 6:19 PM on December 6, 2010 [1 favorite]
You might then have to do a transpose to turn rows into columns, depending on how it's set up?
But other than that, he's spot-on -- get these things out of one cell.
posted by brainmouse at 6:19 PM on December 6, 2010 [1 favorite]
Hi Guys, thanks for your tips! I got defcom1's formula to work, and I was able to run text to columns and transpose as well. This does help me automate comparison of surnames, but I'm not sure how to implement this having each surname in its own cell approach with the file I've been using. The issue is that I'm comparing attributes of various people: segments of DNA that they share with me and each other as well as their surnames. This is information gathered from 23andme testing.
Simplifying a bit, the excel file I've created looks like this:
columns: Person's name, DNA match: chromosome # and location start & end, person's surnames. Each row is devoted to a person. Pardon my table formatting here, it seems to have collapsed a bit.
For example:
Name chromosome Start End Surnames
Joe Smith 1 4,000,000 7,000,000 Hayes, Miller, Smith, Williams
Sally Jones 1 5,000,000 9,000,000 Hall, Jackson, Jones, Martin, Miller, Smith
Paul Douglas 1 15,000,000 22,000,000 Brown, Douglas, Martin
Dave Hayes 5 21,000,000 26,000,000 Allen, Brown, Hayes, Jackson, Miller
As I mentioned, I've got all of each person's surnames in a cell.
Now, compared to the simplified example above, my file has about 1,000 people/rows, and an average of about 30 surnames per person (and over 100 surnames/person in several cases). Having the surnames for each person lumped into a cell has worked for me so far, but does not scale well. :( I set the spreadsheet up that way because I don't know much about Excel. So, I'm not sure how to rework my name-packed cells with a cell for each name. Perhaps I could place each person's names in their own cell, moving to the right of the spreadsheet. Applying that to the example above would create something like this:
[A Name] [B chr.] [C Start] [D End] [E] [F] [G] [H] [I] [J]
Joe Smith 1 4,000,000 7,000,000 Hayes Miller Smith Williams
Sally Jones 1 5,000,000 9,000,000 Hall Jackson Jones Martin Miller Smith
I suppose that once I broke each name block into individual cells, I could set use the ISNA equation to do the person to person comparisons. However, I'd like to do more than compare 2 sets of names.
Ideally, I want to:
1. Compare each person's surnames with all the other folks' entered names, to get an idea of which names appear more than once for everyone in the file (e.g., in the first example, I see that Miller appears three times, Hayes twice, but Williams appears only once. This may help me to distinguish meaningful names from outliers.)
2. Compare each person's surnames with all the other people who have DNA matches for the same chromosome. Similar rationale to point 1. For example, I see that Martin, Miller and Smith each appear twice on chromosome 1, but not at all on chromosome 5.
3. Compare each person's surnames with people who share the same segment of DNA. In the example above, Joe Smith and Sally Jones share DNA from 5,000,000-7,000,000 on chr. 1. I also see that they share the names Smith and Miller. This suggests that Smith and Miller would be likely clues to a common ancestor.
Also, how hard is it to have Excel count how many times a certain name appears in my file? I'd love to see a summary, again, to get an idea of which names are appearing the most.
I hope this makes sense. Any input would be greatly appreciated. Thanks!
BTW I'm using Excel 2008 for Mac.
posted by pantufla at 10:44 PM on December 6, 2010
Simplifying a bit, the excel file I've created looks like this:
columns: Person's name, DNA match: chromosome # and location start & end, person's surnames. Each row is devoted to a person. Pardon my table formatting here, it seems to have collapsed a bit.
For example:
Name chromosome Start End Surnames
Joe Smith 1 4,000,000 7,000,000 Hayes, Miller, Smith, Williams
Sally Jones 1 5,000,000 9,000,000 Hall, Jackson, Jones, Martin, Miller, Smith
Paul Douglas 1 15,000,000 22,000,000 Brown, Douglas, Martin
Dave Hayes 5 21,000,000 26,000,000 Allen, Brown, Hayes, Jackson, Miller
As I mentioned, I've got all of each person's surnames in a cell.
Now, compared to the simplified example above, my file has about 1,000 people/rows, and an average of about 30 surnames per person (and over 100 surnames/person in several cases). Having the surnames for each person lumped into a cell has worked for me so far, but does not scale well. :( I set the spreadsheet up that way because I don't know much about Excel. So, I'm not sure how to rework my name-packed cells with a cell for each name. Perhaps I could place each person's names in their own cell, moving to the right of the spreadsheet. Applying that to the example above would create something like this:
[A Name] [B chr.] [C Start] [D End] [E] [F] [G] [H] [I] [J]
Joe Smith 1 4,000,000 7,000,000 Hayes Miller Smith Williams
Sally Jones 1 5,000,000 9,000,000 Hall Jackson Jones Martin Miller Smith
I suppose that once I broke each name block into individual cells, I could set use the ISNA equation to do the person to person comparisons. However, I'd like to do more than compare 2 sets of names.
Ideally, I want to:
1. Compare each person's surnames with all the other folks' entered names, to get an idea of which names appear more than once for everyone in the file (e.g., in the first example, I see that Miller appears three times, Hayes twice, but Williams appears only once. This may help me to distinguish meaningful names from outliers.)
2. Compare each person's surnames with all the other people who have DNA matches for the same chromosome. Similar rationale to point 1. For example, I see that Martin, Miller and Smith each appear twice on chromosome 1, but not at all on chromosome 5.
3. Compare each person's surnames with people who share the same segment of DNA. In the example above, Joe Smith and Sally Jones share DNA from 5,000,000-7,000,000 on chr. 1. I also see that they share the names Smith and Miller. This suggests that Smith and Miller would be likely clues to a common ancestor.
Also, how hard is it to have Excel count how many times a certain name appears in my file? I'd love to see a summary, again, to get an idea of which names are appearing the most.
I hope this makes sense. Any input would be greatly appreciated. Thanks!
BTW I'm using Excel 2008 for Mac.
posted by pantufla at 10:44 PM on December 6, 2010
You should put each name in it's own cell. It will make life much easier. This can be done with the text to columns function brainmouse referenced.
To find out the frequency of each name in the list, I would copy all the names into a one single column (column A) of a new worksheet, then copy that list again into column B (or add a few spacer columns if you like). Highlight the second column and select 'Remove Duplicates' under the Data tab. (Note: Don't expand the selection when prompted.) This should generate a list of all unique names in your table. Then in column C, you could put a formula like:
=Countif($A:$A,[First Name in Column B])
Then drag fill this to the bottom of the list. This will show how many time that name shows up in the list. I would then highlight both of these columns and convert it into a table (Under the Insert table) so that I could easily sort them.
If you have a ton of lines and aren't really into condensing them by hand, you can copy the entire block of cells into Word and replace the paragraph symbol with a tab. I would also take advantage of this time to scrub any extraneous spaces out of the data.
Also, you can have Excel highlight duplicate values. Highlight a series of date (it can be in multiple rows and columns, then on the Home Tab, select Conditional Formatting, highlight cells rules duplicate values.)
I would need to think some more on how to do the rest of your frequency analysis. If you need more help with how to the other steps I mentioned, please free free to PM me.
posted by gagoumot at 10:23 AM on December 7, 2010
To find out the frequency of each name in the list, I would copy all the names into a one single column (column A) of a new worksheet, then copy that list again into column B (or add a few spacer columns if you like). Highlight the second column and select 'Remove Duplicates' under the Data tab. (Note: Don't expand the selection when prompted.) This should generate a list of all unique names in your table. Then in column C, you could put a formula like:
=Countif($A:$A,[First Name in Column B])
Then drag fill this to the bottom of the list. This will show how many time that name shows up in the list. I would then highlight both of these columns and convert it into a table (Under the Insert table) so that I could easily sort them.
If you have a ton of lines and aren't really into condensing them by hand, you can copy the entire block of cells into Word and replace the paragraph symbol with a tab. I would also take advantage of this time to scrub any extraneous spaces out of the data.
Also, you can have Excel highlight duplicate values. Highlight a series of date (it can be in multiple rows and columns, then on the Home Tab, select Conditional Formatting, highlight cells rules duplicate values.)
I would need to think some more on how to do the rest of your frequency analysis. If you need more help with how to the other steps I mentioned, please free free to PM me.
posted by gagoumot at 10:23 AM on December 7, 2010
Thanks for the suggestions, gagoumot. I will try them out.
posted by pantufla at 10:19 AM on December 10, 2010
posted by pantufla at 10:19 AM on December 10, 2010
« Older I'm worried about talking too much/too little with... | Winterproofing Blunnies Newer »
This thread is closed to new comments.
Finding common text between billsMarch 1, 2011
Help me organize my text files!January 10, 2011
How can I find common words in two lists?December 13, 2010
iPhone and a Mac? Etc?June 5, 2010
What easy to use (and free) text editor and ftp...June 14, 2007
Help me organize my text files!January 10, 2011
How can I find common words in two lists?December 13, 2010
iPhone and a Mac? Etc?June 5, 2010
What easy to use (and free) text editor and ftp...June 14, 2007