Removing last specific character or characters

[jamobe]

Hi

I am using the following formula to remove a ";" from last character of a string if it exists

=IF(RIGHT(A1,1)=";",LEFT(A1,LEN(A1)-1),A1)

What I am struggling with is trying to nest this so that if the data had 2 semi colons it would also delete these

For example the data I have is as follows

EXAMPLE1;EXAMPLE1;EXAMPLE1

EXAMPLE2;EXAMPLE2;

EXAMPLE3;;

Is there a formula to remove the semi colons, at the end only and not to remove any other characters? Any help would be much appreciated!

Many Thanks

Jermaine

 

[Peter_SSs]

Here is another array-entered** formula that will work for any number of trailing semi-colons...

Could be useful for other readers** without Excel 365 but OP does have 365 so
- would not need to array enter and
- in any case can use the fewer-function-call formula at the bottom of post 5.
.. or if the target cell(s) could be blank:

=LEFT(A1,LEN(A1)-SUM(--(RIGHT(A1,SEQUENCE(LEN(A1)+1))=REPT(";",SEQUENCE(LEN(A1)+1)))))

** Without a check for empty cells it does have quite a massive calculation burden if it has to evaluate a number of them with this twice in each formula.
ROW(INDEX(A:A,1):INDEX(A:A,0))

 

[Rick Rothstein]

Could be useful for other readers without Excel 365

That was my main intent given that I am still using XL2010.