Excel 2000 - Find the text in a column that does not have a period.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cmarshall - 17 Jan 2006 21:14 GMT

I have several thousand records where there is a column for middle
initials.

Every so often the middle initial does not have a period.

Is there a way I can find those cells (or maybe highlight them), so I
can add the periods?

Signature

cmarshall

Reply to this Message

Beege - 17 Jan 2006 21:28 GMT

cmarshall

Select the column, Data, Filter, Autofilter.

Custom filter, does not contain (a period)

Beege

> I have several thousand records where there is a column for middle
> initials.
[quoted text clipped - 3 lines]
> Is there a way I can find those cells (or maybe highlight them), so I
> can add the periods?

Reply to this Message

Dave Peterson - 17 Jan 2006 21:31 GMT

I'd insert a column directly to its right.

Then I'd use a formula like:
=c2 & if(c2="","",if(right(c2,1)=".","","."))

then copy it down the column.

Then select that range (and double check the results)
edit|copy
select the original range
edit|paste special|values

And delete that helper column.

> I have several thousand records where there is a column for middle
> initials.
[quoted text clipped - 9 lines]
> cmarshall's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30575
> View this thread: http://www.excelforum.com/showthread.php?threadid=502217

Signature

Dave Peterson

Reply to this Message

cmarshall - 17 Jan 2006 22:20 GMT

Beege - I'll have to go with your answer.

Dave Peterson - your answer is very useful and I'm glad to have the
tip, but I didn't say in my earlier post that there were not only
initials in that column, but also regular names without periods, too.

So I did the autofilter, then sorted on that column and all the
initials were in alpha order, 1st words for each letter and were easy
to find.

Can Dave's formula be modified to look at the cell, but only give the
one's with only 1 letter in the cell?

Signature

cmarshall

Reply to this Message

Dave Peterson - 17 Jan 2006 22:45 GMT

=if(len(c2)<>1,c2,c2&".")

Might work.

> Beege - I'll have to go with your answer.
>
[quoted text clipped - 14 lines]
> cmarshall's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30575
> View this thread: http://www.excelforum.com/showthread.php?threadid=502217

Signature

Dave Peterson

Reply to this Message