| View previous topic :: View next topic |
| Author |
Message |
au79
Newbie
Joined: 04 Sep 2008
Posts: 2
|
 Posted: Thu Sep 04, 2008 5:49 pm Post subject: Finding a specific text string within a cell |
 |
|
Hi all,
I've searched long in this forum for a solution, but to no avail. My goal is to identify a particular text string and display it on the adjacent cell.
Column A has these items:
LC51M [Magenta]
LC51Y [Yellow]
LC51BK [Black]
LC51C [Cyan]
In column B I tried different variations of this formula:
=IF(find("Magenta";A1)=".*\[Magenta\].*";"Magenta";"")
But nothing works. I realized that FIND() returns a numeric value and not a text string. How can I isolate the color names and display them in their own column?
Any help will be greatly appreciated. |
|
| Back to top |
|
 |
uros
Super User
Joined: 22 May 2003
Posts: 601
Location: Slovenia
|
| Posted: Thu Sep 04, 2008 11:01 pm Post subject: |
|
|
Hi au79!
| au79 wrote: | How can I isolate the color names and display them in their own column?
|
Put this formula in B1 and copy down...=MID(A1;FIND("[";A1)+1;FIND("]";A1)-FIND("[";A1)-1) Uros |
|
| Back to top |
|
|
keme
Moderator
Joined: 30 Aug 2004
Posts: 2730
Location: Egersund, Norway
|
| Posted: Fri Sep 05, 2008 4:25 am Post subject: Re: Finding a specific text string within a cell |
|
|
Another important thing to note: | au79 wrote: | [ ... ]
=IF(find("Magenta";A1)=".*\[Magenta\].*";"Magenta";"") |
If you need more advanced text searching, you can use regular expressions, as you try to do with the compare here.
FIND() doesn't support the use of regular expressions, and as far as I can see, compare operations don't either.
On the other hand, SEARCH() does support the use of regex. |
|
| Back to top |
|
|
au79
Newbie
Joined: 04 Sep 2008
Posts: 2
|
| Posted: Fri Sep 05, 2008 7:14 am Post subject: Thanks! |
|
|
Thank you uros and keme for your prompt response.
Uros solution worked perfectly (although I don't understand how its doing it).
Thanks a million. |
|
| Back to top |
|
|
RickRandom
Super User
Joined: 27 Jan 2006
Posts: 1082
Location: UK
|
| Posted: Fri Sep 05, 2008 7:25 am Post subject: Re: Thanks! |
|
|
| au79 wrote: | | Uros solution worked perfectly (although I don't understand how its doing it) |
My understanding is that it FINDs the positions of the 2 square brackets and then gets the text in the MIDdle of them. |
|
| Back to top |
|
|
|
