Prevent proper case of letters following a comma

If there's only 1 apostrophe in the string, you can use:

=REPLACE(PROPER(A1),FIND("'",A1)+1,1,LOWER(MID(A1,FIND("'",A1)+1,1)))

If only an s will follow an '

how about,
=SUBSTITUTE(PROPER(A1),"'S","'s")

Thank you NBVC!

If I understand correctly, this will only work if there is an apostophe present in the text in A1.

Is there a way to make this conditional upon the occurance of an apostrophe in A1? For example, if A1 contains the text, "2-cents".

Thanks again!

If only an s will follow an '

how about,
=SUBSTITUTE(PROPER(A1),"'S","'s")

Click to expand...

Yeah, I should've thought about that too....I guess most likely only an "s" would follow the apostrophe.... This way if multiple 's combinations appear they can all be substituted at once.

Thank you NBVC!

If I understand correctly, this will only work if there is an apostophe present in the text in A1.

Is there a way to make this conditional upon the occurance of an apostrophe in A1? For example, if A1 contains the text, "2-cents".

Thanks again!

Click to expand...

You can do that this way:

=IF(ISNUMBER(FIND("'",A1)),REPLACE(PROPER(A1),FIND("'",A1)+1,1,LOWER(MID(A1,FIND("'",A1)+1,1))),PROPER(A1))


I am not sure if you saw the solution provided by PA HS Teacher, it's simpler and better if you always have 's....then you would use.

=IF(ISNUMBER(FIND("'",A1)),SUBSTITUTE(PROPER(A1),"'S","'s"),PROPER(A1))

Thank you PA HS Teacher!

Yes - I anticipate only the letter "s" will follow an apostrophe. That seems to do the trick.

If there was any possibility of other letters following an apostrophe (e.g. in word's like can't, we'll etc.) perhaps try

=SUBSTITUTE(PROPER(SUBSTITUTE(A1,"'","z-z")),"z-Z","'")

If there was any possibility of other letters following an apostrophe (e.g. in word's like can't, we'll etc.) perhaps try

=SUBSTITUTE(PROPER(SUBSTITUTE(A1,"'","z-z")),"z-Z","'")

Click to expand...

Neat trick Barry!

Thank you all very much!!

If you're interested, here's how I used all of your advice:

I have a column full of data (A1:A100). One cell in the range contains the text, "D.B.A.:" and the cell immediately below it contains the text, "ELMER'S, INC." Using this formula:

=SUBSTITUTE(PROPER(INDEX(Data!$A$1:$A$100,MATCH("D.B.A.:",Data!$A$1:$A$100,0)+1)),"'S","'s")

I get this result: "Elmer's, Inc."

Another cell in the range contains the text, "ELMERS SHOP". I realize the data entry person failed to include an apostrophe, but that doesn't matter for my purposes. This cell will either contain a DBA, or it will contain a string of text beginning with the word, "Insured". Using this formula:

=IF(A3="Insured*","",SUBSTITUTE(PROPER(A3),"'S","'s"))

returns this result: Elmers Shop (when a DBA is present, otherwise, it remains blank).

I appreciate all of you for your help on this. I certianly couldn't have figured it out on my own.