Find letter sorounded by spaces?

[CreativeUsername]

Hi,

I have a HUGE table of data and one column is all kinds of names that need to be parsed. I found two common types of arrangement.

Last Name Mid initial first name (IE: Doe P Jane), the reverse order of that (Jane P Doe), and some variant that puts the mid initial at the end (Doe Jane P or Jane Doe P)

I'd like to be able to sort the data set to focus on all the ones where there is a single letter surrounded by spaces.

I was trying to work with =MID(Table1[@LASTNAME],SEARCH(" ",Table1[@LASTNAME])+1,2)" BUT this cuts out the first 2 letters of the middle word. (sometimes the names are full like Jane Phyllis Doe).

It would help me move through the data faster if I could filter to all items with a stand alone letter.

Any Help would be appreciated.

 

[Tetra201]

See if this helps you:

=ISNUMBER(SEARCH("*? ? ?*",Table1[@LASTNAME]))

 

[CreativeUsername]

Tetra: Awesome.. works well.

 

[Tetra201]

Glad to have helped.

 

[CreativeUsername]

Oh wait.... Not quite. Gives false negatives. if the initial is at the end of the string then it backfires. probably because there is technically not a space after the letter. Is there a way around that?

 

[Tetra201]

Try this:

=OR(ISNUMBER(SEARCH("*? ? ?*",Table1[@LASTNAME])),LEFT(RIGHT(Table1[@LASTNAME],2))=" ")

 

[CreativeUsername]

No. it fails on "Smith James A" If the initial is anywhere else it seems to be fine.

 

[Tetra201]

Works OK in my test workbook.
Apparently, in your dataset, it's not exactly "Smith James A".

Could you please provide answers to the following two questions:
1. What is the result of =LEN(cell_under_question)? -- Is it 13 or something else?
2. What is the result of =CODE(MID(cell_under_question,FIND("es",cell_under_question)+2,1))? -- Is it 32 or something else?

 

[Rick Rothstein]

No. it fails on "Smith James A" If the initial is anywhere else it seems to be fine.

Try this modification to Tetra201's first formula...

=ISNUMBER(SEARCH("* ? *",Table1[@LASTNAME]&" "))