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

 

[eduzs]

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

 

[JLGWhiz]

How about

=Substitute(A1,";","")

Should work for one or multiple occurences.

 

[eduzs]

In this case I only know how to do it with VBA macro.

Public Function TestX(sTmp As String)
Dim x As Long
For x = Len(sTmp) To 1 Step -1
    If Mid(sTmp, x, 1) = ";" Then sTmp = Left(sTmp, x - 1) Else Exit For
Next x
TestX = sTmp
End Function

=TestX(A1)

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

This would allow for up to 5 semicolons at the end. Could be extended of course**. Depending on your Excel version, you may need to confirm this formula with Ctrl+Shift+Enter, not just Enter.

20 09 14.xlsm
	A	B
1	abc	abc
2	abc;	abc
3	abc;;	abc
4	ab;c	ab;c
5	a b;	a b
6	a;b;c;;	a;b;c
7	;a ;b ;c ;	;a ;b ;c
8	ab;;c;	ab;;c
9	a;;;;;b;;;;;c;;;;;	a;;;;;b;;;;;c
Remove trailing semicolons

Cell Formulas
Range	Formula
B1:B9	B1	=LEFT(A1,LEN(A1)-SUM(--(RIGHT(A1,{1,2,3,4,5})=REPT(";",{1,2,3,4,5}))))

** If you have Excel 365 with the SEQUENCE() function then this would allow for any number of trailing semicolons.

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

 

[Peter_SSs]

If there is a maximum of 2 semicolons at the end then of course the post 2 suggestion should work fine, though it could be shortened a little to

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

 

[jamobe]

Wow, thanks for everyones help, Both Peter and Eduzs work exactly how I need it and will save both just in case I run into problems. Will update my profile on here also, I do have Excel 365. JLGwhiz, I tried this one and it removes all semicolons, not just the end ones unfortunately. Thanks again guys!

 

[Peter_SSs]

Wow, thanks for everyones help

You're welcome. Glad we could help.

If there is a maximum of 2 semicolons at the end ... it could be shortened a little to

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

.. or further shortened to

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

 

[Rick Rothstein]

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

=LEFT(A1,MAX(IF(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)<>";",ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))))

**Commit this formula with CTRL+SHIFT+ENTER and not just Enter by itself

 

[sandy666]

Raw	Trim
EXAMPLE1;EXAMPLE1;EXAMPLE1	EXAMPLE1;EXAMPLE1;EXAMPLE1
EXAMPLE2;EXAMPLE2;	EXAMPLE2;EXAMPLE2
EXAMPLE3;;	EXAMPLE3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Trim = Table.AddColumn(Source, "Trim", each Text.TrimEnd([Raw], ";"))
in
    Trim