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

  • Thread starter Thread starter cmarshall
  • Start date Start date
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?
 
cmarshall

Select the column, Data, Filter, Autofilter.

Custom filter, does not contain (a period)

Beege
 
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.
 
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?
 
=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?
Click to expand...
 
Back
Top