Help Needed in Splitting the Address data in one cell to multiple cells

[ajayxlnc]

[pre]

Hello,

Help needed in figuring out a solution for my problem.

I have a data which contains Sl.No, Mobile No, Email Id, Address Main (4 Columns).
In "Address Main" Column the entire address is entered in a single cell

Sample:
(Xxxxxx yyyyy.Zzzzzzz H.no: 1 10 100/90/B/40, Www Wwwwwwwww wwwwwwww,Vvvvvvvv Vvvvvvvv, Uuuuuu uuu, Hyderabad90-909191919191)

I tried to used Delimiter and tried to Split it with the help of " , " or " - " and it was a failure. There are around 3562 records and the data in the address field is not proper in most of the cells.

Is there a way i can split the field in Column D into different cells, for example i want the data in this way (Using the above sample address cell)

E                       F                G                      H
Xxxxxx yyyyy.Zzzzzzz    1 10 100/90/B/40 Www Wwwwwwwww wwwwwwww Vvvvvvvv Vvvvvvvv Uuuuuu uuu

P.S : But for the above sample every details is seperated by " , " but for many other cell it is not the case. That is the main challenge for me.

Can you help me out with a solution

Sample Document

https://docs.google.com/file/d/0B_8nXiEldUKKa3g5QzJwMUk4blU/edit?usp=sharing

EDITED: changed personal data to dummy values.

[/pre]

 

[Faseeh]

Hi ajayxlnc,


I do't have a solution but suggestion, kindly do not post data that is confidential or it may be unimportant to you but someone else might use it, for purpose other then solving an excel problem (eg. for SPAM). Your link contains some 3500+ cell numbers/phone numbers, email ids and addresses of some people. Post that is random or just a sample of it.


it seems that this is not the manipulated data, i guess it is original one!


Regards.

 

[ajayxlnc]

Thanks For the suggestion.


But this data is Original, this is not a SPAM.


Help needed !!!!!

 

[bobhc]

ajayxlnc


You do realise that many countries have data protection laws and to publish personal details can lead to a very heavy fine. It is all ways best to use something like the rand function in your columns to randomize numbers and names, doing this would protect your clients information but would not stop forum members from helping.

It may be urgent to you but the forum members have life's to lead and it may not be urgent to them, Urgent removed from post title and from the tag list.


You say there is around 3562 records, that is one hell of a spammers list.


I suggest you read Chandoos post


http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

 

[MikeLanglois]

You need to clean up your data more efficiently and it would make the task much more manageable.


I was going to suggest separating it by spaces, but the delimiters range too much.


If you can, set up some sort of cleaning process as it comes in to excel from whichever source, that way it will be much better. As it stands, your data looks too dirty to clean.


Also, as above, make sure to remove all personal information from the spreadsheets lol

 

[SirJB7]

Hi, ajayxlnc!

I strongly recommend you to remove the uploaded file (even I see that it now hasn't public access granted) and post a new sample file according to the above link.

If not and even you give public access or access upon request to your file, maybe there are people who would refrain from posting a solution as myself.

Regards!

 

[ajayxlnc]

Hello,


I have already deleted the file. Sorry for not checking the content before uploading a sample data.


In Cell D2 i have placed the sample address which i have in my data sheet. There is Char(13) in between each and every field i tried all the ways but am not able to remove that charecter from that cell. My Addresses have lot of challenges like in one cell each String is seperated by space in one cell and by Char(13) in other and by "," in other and by many types of special charecters ! I tried many VBA codes but of no use i am not able to find out a proper solution where in i can seperate the address content in a single cell and split it into multiple cells based on Address1, Address2, Address3, City, State, PinCode, LandMark, Etc.


Is there any work around for this kind of challenges, There are around 3500 records, i can do it manually but this type of data i will receive twice in a week, so i am looking out for a generic solution.


Waiting for your suggestions.


Sample Data (Only Sample)


https://docs.google.com/file/d/0B_8nXiEldUKKUjBLLWdiZjVmYVU/edit?usp=sharing

 

[SirJB7]

Hi, ajayxlnc!

Try this:

Select column D, go to the Data tab, Data tools group, Text In Columns icon, select Delimited option, set delimiters as Tab, Comma and Semicolon, and that's all.

Regards!

PS: I edited your first post.

 

[Miller C]

Hi, I am on my phone so cannot check the data properly, but I would try SirJB7's solution first. If that doesn't work you can use left and mid formulas so long as the data is consistent.

For example, you have columns E, F, G and H, you use these formulas:

E2: left(D2,20)

F2: mid(D2,21,16)

G2: mid(D2,37,22)

H2: mid(D2,58,28) or possibly right(D2,28)


If you're interested, the detail in the formulas mean:

Left(Text,Number of characters) shows left number of characters in the cell.

Right(Text,Number of characters) shows right number of characters in the cell.

Mid(Text,Start point,Number of characters) shows middle number of characters, choosing where the start point is.


Apologies if Ive got the number of characters wrong, but play around with that and amend as required. Let me know how you get on!