How can I combine these two formulas into one

[Bassie]

Hi,

So I basically wanna do two different things in the same formula. I got a working formula for both but I don't know how to combine them. I have a column full of sentences and what I want to do is to uppercase the first letter of the sentence and remove a . at the end of the sentence (if there is one).

Uppercasing only the first letter of a sentence:
=UPPER(LEFT(E19,1))&RIGHT(E19,LEN(E19)-1)

Removing the . after a sentence (if there is one)
=IF(RIGHT(E19,1)=".",LEFT(E19, LEN(E19) - 1),E19)

Is there a way to combine them? I am not a huge fan of helper columns as I would like my worksheets to look clean.

Thanks a lot in advance!

 

[JamesCanale]

=UPPER(LEFT(IF(RIGHT(E19,1)=".",LEFT(E19, LEN(E19) - 1),E19),1))&RIGHT(IF(RIGHT(E19,1)=".",LEFT(E19, LEN(E19) - 1),E19),LEN(IF(RIGHT(E19,1)=".",LEFT(E19, LEN(E19) - 1),E19))-1)

 

[Fluff]

Another option

=REPLACE(IF(RIGHT(E19,1)=".",LEFT(E19, LEN(E19) - 1),E19),1,1,UPPER(LEFT(E19)))

Or if the . will only ever appear at the end of the text

=REPLACE(SUBSTITUTE(E19,".",""),1,1,UPPER(LEFT(E19)))

 

[JvdV]

=UPPER(LEFT(E19))&MID(E19,2,LEN(E19)-1-(RIGHT(E19)="."))

 

[Bassie]

Another option

=REPLACE(IF(RIGHT(E19,1)=".",LEFT(E19, LEN(E19) - 1),E19),1,1,UPPER(LEFT(E19)))

Or if the . will only ever appear at the end of the text

=REPLACE(SUBSTITUTE(E19,".",""),1,1,UPPER(LEFT(E19)))

Thank you so much!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Bassie]

Glad we could help & thanks for the feedback.

Thanks so much again!

I had a small follow up question, is it also possible to remove any ; or , at the end of the sentence?

If not still massive thanks! this community is amazing.

 

[Fluff]

Will those only ever appear at the end of the text, or could they also be in the middle?

 

[JvdV]

One option:

=UPPER(LEFT(E19))&MID(E19,2,LEN(E19)-1-SWITCH(RIGHT(E19),".",1,",",1;";",1,0))

Another is:

=UPPER(LEFT(E19))&MID(E19,2,LEN(E19)-1-ISNUMBER(FIND(RIGHT(E19),".,;")))

 

[Bassie]

Will those only ever appear at the end of the text, or could they also be in the middle?

Only at the end, sorry forgot to mention