CF formula needed to highlight text not containing unique format in a column

[XLWiz]

Hello everyone,

I've been racking my brain trying to figure this out. I have a census list, containing over 1000+ names. What I need, is a CF formula that will allow me to highlight text entered in a specific way.

For example, if cell G2 contains the name "John B Smith", and G3 contains "Jane Mary Smith", I want to highlight the cell that contains the full name.

If this cannot be accomplished, a formula that will highlight the latter format will do. Any assistance that can be provided is appreciated.

Thanks.

 

[JoeMo]

Hello everyone,

I've been racking my brain trying to figure this out. I have a census list, containing over 1000+ names. What I need, is a CF formula that will allow me to highlight text entered in a specific way.

For example, if cell G2 contains the name "John B Smith", and G3 contains "Jane Mary Smith", I want to highlight the cell that contains the full name.

If this cannot be accomplished, a formula that will highlight the latter format will do. Any assistance that can be provided is appreciated.

Thanks.

This assumes all your names are in one of the two formats you have cited.
1. Select all the cells with names
2. Home>Conditional Formatting>New Rule> Use a formula

Formula is:

=LEN(TRIM(MID(SUBSTITUTE(G2," ",REPT(" ",100)),100,100)))>1

3. Choose the format and click OK

 

[XLWiz]

This worked great however, what I needed was a formula that would highlight names that were not in the "John B Smith" format. What do I need to adjust in your formula to get this result?

Thanks again for your help.

 

[JoeMo]

This worked great however, what I needed was a formula that would highlight names that were not in the "John B Smith" format. What do I need to adjust in your formula to get this result?

Thanks again for your help.

What your original post states is: "For example, if cell G2 contains the name "John B Smith", and G3 contains "Jane Mary Smith", I want to highlight the cell that contains the full name." I assumed that means the middle initial is replaced by the full middle name.

Can you post some examples that cover the spectrum of name formats in your list and indicate which you would like to highlight?

 

[XLWiz]

Sure thing. For example, if the list contained the names:

John B Smith
Mary Jane Smith > Highlighted
Bob T Rudy
Elmer J Fudd
Elizabeth Wanda Jones > Highlighted

Basically, the names not following the "John B Smith" format are highlighted. Make sense? Thanks again.

 

[JoeMo]

Sure thing. For example, if the list contained the names:

John B Smith
Mary Jane Smith > Highlighted
Bob T Rudy
Elmer J Fudd
Elizabeth Wanda Jones > Highlighted

Basically, the names not following the "John B Smith" format are highlighted. Make sense? Thanks again.

That was my understanding of what you wanted from your original post. What I provided does exactly that if you follow the steps I provided.

 

[XLWiz]

I followed your steps to a T, and am very familiar with CF rules, but am still seeing several names that are not in the "John B Smith" format not highlighting for some reason...

 

[JoeMo]

I followed your steps to a T, and am very familiar with CF rules, but am still seeing several names that are not in the "John B Smith" format not highlighting for some reason...

Can you post a sample of the names that are not highlighting? Did you select the cells they are in before setting the CF? Are you using a relative address in the CF formula?

 

[XLWiz]

JoeMo,

Please disregard. I found a leading space in several of the names and realized this was the reason the CF was not highlighting them. I'm in the process of correcting that now.

Thanks again for your help.

 

[JoeMo]

JoeMo,

Please disregard. I found a leading space in several of the names and realized this was the reason the CF was not highlighting them. I'm in the process of correcting that now.

Thanks again for your help.

You are welcome.

If you want to leave the leading space(s) just change the CF formula to this:

=LEN(TRIM(MID(SUBSTITUTE(TRIM(G2)," ",REPT(" ",100)),100,100)))>1