Finding Text in a String

  • Thread starter Thread starter Wendy L
  • Start date Start date
W

Wendy L

Windows XP
Office XP

I wish to format a column of text from all caps to Proper case. I have no
trouble doing this with the proper function except that it does not take
into account names like McCartney where a capital letter is needed in the
middle of the text. I was able to overcome this if the name McCartney occurs
at the beginning of the text string with the formula:

=IF(LEFT(A2,2)=""Mc"",PROPER(LEFT(A2,2))&PROPER(RIGHT(A2,LEN(A2)-2)),PROPER(
A2))

However if the text McCartney occurs in the middle of a text string, like
"Estate of McCartney Children" I must admit I get rather lost when trying to
find a method of finding "Mc" in the middle of the text string and using
Proper function on the remainder of the text. If I could see it done once,
am certain I would have a better understanding of the method on how to
achieve this result. Any suggestions?
 
Windows XP
Office XP

I wish to format a column of text from all caps to Proper case. I have no
trouble doing this with the proper function except that it does not take
into account names like McCartney where a capital letter is needed in the
middle of the text. I was able to overcome this if the name McCartney occurs
at the beginning of the text string with the formula:

=IF(LEFT(A2,2)=""Mc"",PROPER(LEFT(A2,2))&PROPER(RIGHT(A2,LEN(A2)-2)),PROPER(
A2))

However if the text McCartney occurs in the middle of a text string, like
"Estate of McCartney Children" I must admit I get rather lost when trying to
find a method of finding "Mc" in the middle of the text string and using
Proper function on the remainder of the text. If I could see it done once,
am certain I would have a better understanding of the method on how to
achieve this result. Any suggestions?
Click to expand...

=IF(ISERROR(FIND("MC",A1)),PROPER(A1),
PROPER(LEFT(A1,FIND("MC",A1)+1))&
PROPER(MID(A1,FIND("MC",A1)+2,255)))

might work.


--ron
 
Thank you, that worked perfectly! Looks like your formula says if finding
the text Mc returns an error, proper case cell A1, else proper everything in
cell A1 up to "Mc" and then proper everything after "Mc". It looks so simple
when you do it...lol.


 
Thank you, that worked perfectly! Looks like your formula says if finding
the text Mc returns an error, proper case cell A1, else proper everything in
cell A1 up to "Mc" and then proper everything after "Mc". It looks so simple
when you do it...lol.
Click to expand...

That's true. But note that FIND is case sensitive, so we are looking for "MC"
and not "Mc". You did state that all of the entries were upper case.

Glad to help.


--ron
 
Back
Top