How to remove Trailing Special Characters

Hi,

I have a requirement to remove the trailing semicolons in a column.

The ";" characters have no fixed location and they may occur once or multiple times or may not be present at the end of the text at all.

Example:
TEST 01;;;NAME;ADDRESS;;;;
TEST 02;;;;ADDRESS STREET;
TEST 03;;;COUNTRY;;;
TEST 04;

How do I remove the trailing ";" characters?

The result I'm after are:
TEST 01;;;NAME;ADDRESS
TEST 02;;;;ADDRESS STREET
TEST 03;;;COUNTRY
TEST 04

I have tried using combinations of LEFT, FIND, and some SUBSTITUTE but it also removed the ";" in between the texts.

Please Login or Register  to view this content.

Cheers,

Donard

http://www.mrexcel.com/forum/excel-q...-one-go-2.html

Check This Above Thread Please

Originally Posted by sandy666

something like this?
=TRIM(SUBSTITUTE(A1,";"," "))

TEST 01 NAME ADDRESS
TEST 02 ADDRESS STREET
TEST 03 COUNTRY
TEST 04

ups, I lost ;;; between text, sorry

so try:
=LEFT(A1,LOOKUP(2,1/(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz1234567890"))),ROW(INDIRECT("1:"&LEN(A1)))))

TEST 01;;;NAME;ADDRESS
TEST 02;;;;ADDRESS STREET
TEST 03;;;COUNTRY
TEST 04

Hi Sandy, Your solution worked perfectly! Thanks so much.

I'll need to read up on LOOKUP and INDIRECT and add them to my excel vocabulary

Thanks again.

Cheers,

Donard

Glad to help thanks for rep.