Data Cleanup

**harshalcr9** · 12-17-2015, 05:48 AM

Hi,

We are trying to extract
'PALM BEACH' from this address - "35278 A KAMLAI BHAVAN 2074 B DARSHANDARBAR RD SEC 6 PALM BEACH RD NERUL,-400478" same-wise
'NRI COMPLEX' from "2-50345 SEAWOODS ESTATES NRI COMPLEXSEC 54 56 AND 58 ROADNERUL-4007056". There are many more sample had been attached.

We had tried with text to columns but as keyword is not located at same length in each address it can't be used.'Contains' function also can't be used as keyword is not same across each address.

Is there any different approach to this problem.

Attached excel had desired output.

Please let us know anything else is needed for Analysis.

Thanks in advance

Regards,
Ramesh

**brokenbiscuits** · 12-17-2015, 06:43 AM

I'm pretty sure that's going to be impossible unless you use some kind of lookup table.

The only other thing that could work is, if you don't have a large amount of different desired outputs, combining IF and FIND like:

=IF(ISERROR(FIND("NRI",A17)),IF(ISERROR(FIND("PALM BEACH",A17)),IF(ISERROR(FIND("GAON",A17)),,"GAON"),"Palm Beach"),"NRI")

**harshalcr9** · 12-17-2015, 07:42 AM

Thanks for above post ..

Actually we don't have keyword(localities) for each address and even if know keywords for each address, our data is in millions.

Is there any other approach which we see!!

**brokenbiscuits** · 12-17-2015, 07:47 AM

No, absolutely not, Excel needs structured data. If you don't know what you're looking for, then it can't find it.

Data Cleanup

LinkBack

Thread Tools

Rate This Thread

Display

Data Cleanup

Re: Data Cleanup

Re: Data Cleanup

Re: Data Cleanup

Thread Information

Users Browsing this Thread

Similar Threads

Data cleanup: separating values in a column

Cleanup telephone numbers that have some text data

external data - cleanup existing connections

File Cleanup & Insert Data

Inefficient/slow data cleanup code

'data cleanup' problem

Macro to cleanup data

Bookmarks

Bookmarks

Posting Permissions