Consulting

Results 1 to 9 of 9

Thread: Need to remove a certain part of cell Data

  1. #1
    VBAX Regular
    Joined
    Apr 2017
    Posts
    32
    Location

    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. Address Issue.xlsx

    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,

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    You'll need to add to the list of towns either manually or downloading lists .
    Attached Files Attached Files
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Regular
    Joined
    Apr 2017
    Posts
    32
    Location
    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 ?

  4. #4
    VBAX Regular
    Joined
    Apr 2017
    Posts
    32
    Location
    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.

  5. #5
    VBAX Regular
    Joined
    Apr 2017
    Posts
    32
    Location
    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.

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #7
    VBAX Regular
    Joined
    Apr 2017
    Posts
    32
    Location
    Simply Put >> Pure Genius !!! Thank you Sir for making my Life Easy.

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

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Please only include quotes where required to clarify your further questions.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    VBAX Regular
    Joined
    Apr 2017
    Posts
    32
    Location
    Ok Chief... Thank you again. Take care.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •