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!

 

[Fluff]

Borrowing from JvdV's idea how about

=REPLACE(REPLACE(E19,1,1,UPPER(LEFT(E19))),LEN(E19),1,SWITCH(RIGHT(E19),".","",",","",";","",RIGHT(E19)))

 

[Fluff]

Another option if you have Textbefore

=REPLACE(TEXTBEFORE(E19,{".",";",","},-1,,,E19),1,1,UPPER(LEFT(E19)))

 

[JvdV]

Another option if you have Textbefore

=REPLACE(TEXTBEFORE(E19,{".",";",","},-1,,,E19),1,1,UPPER(LEFT(E19)))

@Fluff if that answer is based on the fact that these characters only would occur at the end of a string (if they appear) I'd suggest TEXTSPLIT() instead which won't error out if neither of them is present. I did see you used the last parameter 'if not found' though =).

=REPLACE(TEXTSPLIT(E19,{".",",",";"},,1),1,1,UPPER(LEFT(E19)))

Btw, nice technique with REPLACE().

 

[Fluff]

Another option

=REPLACE(TEXTBEFORE(E19,{".",";",","},,,1),1,1,UPPER(LEFT(E19)))

It appears that the "match end" argument doesn't work if you use a -ve as the instance number

 

[Bassie]

It blows my mind what you two can do in so little time. I dont have the TEXTBEFORE function but

=REPLACE(REPLACE(E19,1,1,UPPER(LEFT(E19))),LEN(E19),1,SWITCH(RIGHT(E19),".","",",","",";","",RIGHT(E19)))

Works perfectly for my usecase.

Many thanks again!

 

[Fluff]

Glad we could help & thanks for the feedback.