Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding specific text in a string
Hi All
I have a hundreds cells with a long text string separated by comma's detailing the all the applications on PC as follows: Column A, Cell A2: PC Reference Column B, Cell B2: Access 2000, Project 2000, Front 2000, Easy CD Creator, Adobe 5.0, etc ....And the list continues to rows 15000 Is there a way I can search the string in B2 to identify 'Project 2000' only and then populate a blank Cell (C2) with 'Project 2000'? Something like: IF the string in Cell B2 contains 'Project 2000' THEN populate Cell C2 with 'Project 2000'. I'd appreciate any advise on how to create such a worksheet function. Thanks in advance, H |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding specific text in a string
On Sat, 8 Apr 2006 03:51:01 -0700, Hardip
wrote: Hi All I have a hundreds cells with a long text string separated by comma's detailing the all the applications on PC as follows: Column A, Cell A2: PC Reference Column B, Cell B2: Access 2000, Project 2000, Front 2000, Easy CD Creator, Adobe 5.0, etc ...And the list continues to rows 15000 Is there a way I can search the string in B2 to identify 'Project 2000' only and then populate a blank Cell (C2) with 'Project 2000'? Something like: IF the string in Cell B2 contains 'Project 2000' THEN populate Cell C2 with 'Project 2000'. I'd appreciate any advise on how to create such a worksheet function. Thanks in advance, H C2: =IF(ISNUMBER(FIND("Project 2000",B2)),"Project 2000","") You can replace "Project 2000" with a cell reference where the cell contains the application name. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding specific text in a string
Try a combination of FIND() and ISERROR():
=IF(ISERROR(FIND("happy",B1,1)),"","happy") or if using the text in column A will make you more happy: =IF(ISERROR(FIND(A1,B1,1)),"",A1) -- Gary's Student "Hardip" wrote: Hi All I have a hundreds cells with a long text string separated by comma's detailing the all the applications on PC as follows: Column A, Cell A2: PC Reference Column B, Cell B2: Access 2000, Project 2000, Front 2000, Easy CD Creator, Adobe 5.0, etc ...And the list continues to rows 15000 Is there a way I can search the string in B2 to identify 'Project 2000' only and then populate a blank Cell (C2) with 'Project 2000'? Something like: IF the string in Cell B2 contains 'Project 2000' THEN populate Cell C2 with 'Project 2000'. I'd appreciate any advise on how to create such a worksheet function. Thanks in advance, H |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding specific text in a string
Ron
Many thanks for that - it worked. Can you please explain the ISNUMMBER element of the function? Might as well truely understand how this works :-) Many thanks again, H "Ron Rosenfeld" wrote: On Sat, 8 Apr 2006 03:51:01 -0700, Hardip wrote: Hi All I have a hundreds cells with a long text string separated by comma's detailing the all the applications on PC as follows: Column A, Cell A2: PC Reference Column B, Cell B2: Access 2000, Project 2000, Front 2000, Easy CD Creator, Adobe 5.0, etc ...And the list continues to rows 15000 Is there a way I can search the string in B2 to identify 'Project 2000' only and then populate a blank Cell (C2) with 'Project 2000'? Something like: IF the string in Cell B2 contains 'Project 2000' THEN populate Cell C2 with 'Project 2000'. I'd appreciate any advise on how to create such a worksheet function. Thanks in advance, H C2: =IF(ISNUMBER(FIND("Project 2000",B2)),"Project 2000","") You can replace "Project 2000" with a cell reference where the cell contains the application name. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding specific text in a string
Thanks Gary
I appreciate the response - and it worked!!! Have a good day - H "Gary''s Student" wrote: Try a combination of FIND() and ISERROR(): =IF(ISERROR(FIND("happy",B1,1)),"","happy") or if using the text in column A will make you more happy: =IF(ISERROR(FIND(A1,B1,1)),"",A1) -- Gary's Student "Hardip" wrote: Hi All I have a hundreds cells with a long text string separated by comma's detailing the all the applications on PC as follows: Column A, Cell A2: PC Reference Column B, Cell B2: Access 2000, Project 2000, Front 2000, Easy CD Creator, Adobe 5.0, etc ...And the list continues to rows 15000 Is there a way I can search the string in B2 to identify 'Project 2000' only and then populate a blank Cell (C2) with 'Project 2000'? Something like: IF the string in Cell B2 contains 'Project 2000' THEN populate Cell C2 with 'Project 2000'. I'd appreciate any advise on how to create such a worksheet function. Thanks in advance, H |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding specific text in a string
On Sat, 8 Apr 2006 05:04:02 -0700, Hardip
wrote: Ron Many thanks for that - it worked. Can you please explain the ISNUMMBER element of the function? Might as well truely understand how this works :-) Many thanks again, H You're welcome. If FIND succeeds, it returns a number indicating the position of the found string. So the ISNUMBER is a test for success of the FIND worksheet function. If FIND does not succeed, it returns a #VALUE! error value, so you could also use the ISERR, ISERROR, or ERROR.TYPE functions to test for failure of the function. You should also note that the FIND worksheet function is case-sensitive. For a case-insensitive function, you can use the SEARCH function, which will also accept wild-cards. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste Rows that Contain Specific Text | Excel Worksheet Functions | |||
Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
Text String - Specific Characters | Excel Worksheet Functions | |||
Reomving a set numer of characters from a text string | Excel Discussion (Misc queries) | |||
Remove last character of text string | Excel Worksheet Functions |