Searching strings in Excel sheet

**NewScientist_au** · 04-28-2009, 04:07 AM

Hello all
I am wondering if anyone can help me out with a formula or a macro to do the following:
I have an excel sheet with a list of characters in each cell of the column. is there any way to select and highlight certain characters based on their position.

For example
ABCDEFGH
ADDDDDEB

Can we somehow highlight all cells which have 'A' in the first position and 'G' in the penultimate position? So in theory it should only highlight the first cell.

What sort of commands should I be looking at to do functions like that so I can devise more rules like perhaps 'A' in position 1 and 'D' in position 2 and/or 'E' in position 3 etc
Any direction with some explanation would be greatly appreciated and I am happy to troll through help files to figure out finer details.

Thanks heaps!!!

**DonkeyOte** · 04-28-2009, 04:11 AM

It sounds as though you want to use Conditional Formatting with a Formula Based approach, however, depending on the XL version you are using you are limited in how many test you can run - pre XL2007 you are limited to 3 tests (+ standard format), if you have more than that and are running a pre XL07 version you will need to use VBA.

A sample Conditional Format formula test based on your example would be as follows:

=AND(LEFT(A1,1)="A",MID(A1,LEN(A1)-1,1)="G")
format set accordingly

(where A1 holds first string)

**martindwilson** · 04-28-2009, 05:48 AM

you could be a bit more dynamic in attached sheet put the 2 letters you want to find and in what position then cells will be highlighted
conditional format is
=AND(CHOOSE($D$2,MID(A1,1,1),MID(A1,2,1),MID(A1,3,1),MID(A1,4,1),MID(A1,5,1),MID(A1,6,1),MID(A1,7,1),MID(A1,8,1))=$D$1,CHOOSE($D$4,MID(A1,1,1),MID(A1,2,1),MID(A1,3,1),MID(A1,4,1),MID(A1,5,1),MID(A1,6,1),MID(A1,7,1),MID(A1,8,1))=$D$3)

**NewScientist_au** · 08-20-2009, 10:38 PM

Dear Martin and DonkeyOte
Thanks heaps for your suggestions I only recently got to try them and they work treat. Especially Martin's excel sheet is amazing!
Thanks very much guys and yes I do sincerely apologise for not replying in a very timely fashion!

now looking forward to analysing the data!!!

Have a great weekend!