extract text from 3 column data containing postal address

[SATISH KULKARNI]

Hi,

I have a data like this (postal address in 3 columns with no similarity of pattern)

sr no name address1 address2 address3
1 Mr Xyz At/Post Sonari Tal Karmala Dist. Solapur
2 MR PQR H. No. 212, A/PSonari TQ Karmala DT Solapur
3 MR ABC H NO. 435 A.P.Sonari Tal-Karmala,Dt Solapur

In this example i have quoted name of one of the many villages, I want to prepare seperate list/ sort/ filter on given village name.

Will you please help me.

 

[Hui]

Can you please post a sample file with a dozen typical examples

 

[SATISH KULKARNI]

T

Respected Sir,
I have attached one sample file containg data, in which you will observe Taluka "Malshiras" is in 3 different columns. Kindly guide how I can prepare list of one particular village.

Thank you sir. Hope you will help me.

 

[Muhammad Shakeel Ishaq]

Hey Satish

I hope you are fine, please find attached file, i hope it will work, if you still find any problem, do ask me.

I have concatenate Address 1, Address 2 and Address 3, separated with comma ","

Concatenate merge all three addresses. Than i trim all your three columns, as they have a lot of extra spaces, trim function remove extra space.

After that i put filter and type required district in search field of filter.

Check it and reply

 

[SATISH KULKARNI]

T

Thanks for reply Muhammad shakeel Ishaq.
But I don't think it is so simple as pattern is not similar, data has different pattern like
Tal-Malshiras
TQMalshiras
Tal.Malshiras

I have tried to replace all the pretexts with nothing but some other data is also replaced, if, it has that sequences of characters.

 

[shrivallabha]

Word extraction of any kind is pure pain unless the data has high degree of uniformity and certain rules have been followed. If not then it becomes tedious to get the desired results quickly and reliably.

I would suggest you streamline such issues before going ahead with the following formula setup. If you could inform source to improve on output then perhaps it will become easier to deal with .

I have created a table named "TalukaList" and put the taluka names there. And then I have used following formula in cell F5 and copied it down.
=LOOKUP(199,SEARCH(TalukaList,C5&D5&E5,1),TalukaList)
I have written couple of information comments for the idea I have used. I am attaching the workout I did for getting the result.

Points to remember:
1. There's one place where the result appears as #N/A due to bad input.
2. Also keep in mind that K. Mahankal and K.Mahankal will be two different cases (due to space character in between) if that is the case with your main data.

Hope this helps you.

 

[SATISH KULKARNI]

Thank you Shrivallabha it will work.
Still I think if instead of repeating total taluka name or village name, some logical field say "1" for available "0" for non available of word will do better, but i could not find such command for locating a set of characters in a given text field.

 

[shrivallabha]

Still I think if instead of repeating total taluka name or village name, some logical field say "1" for available "0" for non available of word will do better, but i could not find such command for locating a set of characters in a given text field.

Could you elaborate this?

Are you interested in finding only one Taluka at a time? e.g. You write search criterion in one of the cell and get 1s for matching rows and 0s for non matching rows.

If there's list of Talukas then I do not see 1s and 0s providing any effective pointers.

But the formula can be tweaked to give the result you desire.

 

[SATISH KULKARNI]

Yes I do want to work on one taluka at a time

 

[shrivallabha]

OK. Lets say the cell for entering criteria is cell F3 then in F5 enter following formula and copy down.

=ISNUMBER(LOOKUP(199,SEARCH($F$3,C5&D5&E5)))+0

It should give you desired results.

 

[SATISH KULKARNI]

Thanks Shrivallabha, With this I can easily work on any one village.

Thank you very much.
-satish