Need to remove a certain part of cell Data

Hi Good Afternoon from Sri Lanka,

I have lots and lots of addresses as Data, out of that I need to remove/omit the part which states the City and state along with the rest of the contents like Zip No. etc ANYTHING & EVERYTHING which follows afterwords.

Sample given below for your reference & also has been attached. :)Attachment 18953

Please give me a hand.

Many Thanks.

THE DATA What I need JLDLFSLDJFLSDJJFLKSJL, SCLKSCLSMDCLK, SMCWCLCSLM, Zhongshan, Guangdong, China JLDLFSLDJFLSDJJFLKSJL, SCLKSCLSMDCLK, SMCWCLCSLM, SKDJNCSDCNSC, LSCLCJSDLJCLKCLSCLKSJC, Zhongshan, Guangdong, China Book Special Tour SKDJNCSDCNSC, LSCLCJSDLJCLKCLSCLKSJC, SKDJN, LSCLCJSDLJCLKCLSCLKSJC, Zhongshan, Guangdong, China 528414 Book Special Tour SKDJN, LSCLCJSDLJCLKCLSCLKSJC, JLDLFSLDJFLSDJJFLKSJL, SCLKSCLSMDCLK, SMCWCLCSLM, Zhongshan,.China JLDLFSLDJFLSDJJFLKSJL, SCLKSCLSMDCLK, SMCWCLCSLM, SKDJN, LSCLCJSDLJCLKCLSCLKSJC, Guangdong, China Book Special Tour SKDJN, LSCLCJSDLJCLKCLSCLKSJC, JLDLFSLDJFLSDJJFLKSJL, SCLKSCLSMDCLK, SMCWCLCSLM, Zhongshan City,.China JLDLFSLDJFLSDJJFLKSJL, SCLKSCLSMDCLK, SMCWCLCSLM,

You'll need to add to the list of towns either manually or downloading lists .

Hi Brother, even though what you've managed to achieve here is what exactly I need, I didn't understand what exactly have you done, as I found a list of the towns in another page. Plus we have a button here. Is there any possibility to achieve the same like the last evening (ex: myclear) by the means of function only ?

In case if this is the only solution, can you please let me know,
1. Does the addresses need to be in SAME COLOMN one after the other? if any other data is available will that be affected?
2. Is the Function enabled only for a certain coloumn (like result coloumn only)?

Because I have mentioned city and state names throughout the excel...

Thank you.

Yes Brother, Just as I expected no matter which part in the worksheet, if an address is found, the code seems to recognize it and then trims it. After words it gives the result exactly one colomn after the address colomn, can anything be done to not have a button to run the code and have a unique function for each cell like the solution which you gave me last evening?

Thanks.

Here is a UDF method.
The code contains options to search only column 1 of Towns or the entire sheet.
The code looks at each comma separated word in turn and searched Towns. If it finds a match, the data is trimmed from that point. There is an option to return either the original text or a blank cell in no match is found.

Code:

---

Function RemTown(Data)
    Dim Towns As Range, Fnd As Range
    Dim txt, t
    Dim x As Long


    'Set Towns = Sheets("Towns").Columns(1)
    'or to seach entire sheet
    Set Towns = Sheets("Towns").Cells
        txt = Split(Data, ",")
        For Each t In txt
            On Error Resume Next
            Set Fnd = Towns.Find(Trim(t), lookat:=xlPart)
            If Not Fnd Is Nothing Then
                x = InStr(1, Data, t)
                RemTown = Left(Data, x - 2)
                Exit For
            Else
                RemTown = ""
                'or return original text
                'RemTown = Data
            End If
        Next t
End Function

---

Simply Put >> Pure Genius !!! Thank you Sir for making my Life Easy.

May Allah guide and bless you at all times. Ameen.

Please only include quotes where required to clarify your further questions.

Ok Chief... :) Thank you again. Take care.