PDA

View Full Version : [SOLVED] Need to remove a certain part of cell Data



New_Here
04-17-2017, 11:18 PM
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. :)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,

mdmackillop
04-18-2017, 01:25 AM
You'll need to add to the list of towns either manually or downloading lists .

New_Here
04-18-2017, 02:08 AM
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 ?

New_Here
04-18-2017, 02:33 AM
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.

New_Here
04-18-2017, 02:54 AM
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.

mdmackillop
04-18-2017, 05:44 AM
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

New_Here
04-18-2017, 06:37 AM
Simply Put >> Pure Genius !!! Thank you Sir for making my Life Easy.

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

mdmackillop
04-18-2017, 08:28 AM
Please only include quotes where required to clarify your further questions.

New_Here
04-18-2017, 09:07 AM
Ok Chief... :) Thank you again. Take care.