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

C

cmarshall

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?
 
B

Beege

cmarshall

Select the column, Data, Filter, Autofilter.

Custom filter, does not contain (a period)

Beege
 
D

Dave Peterson

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.
 
C

cmarshall

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?
 
D

Dave Peterson

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

Might work.

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top