Urgent Help Needed in Splitting the Address data in one cell to multiple cells (Excel)

Question

1.80/5 (3 votes)

See more:

Help needed in figuring out a solution for my problem. Can this be done using .NET

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:
(Kishore kumar.Madugmla H.no: 1 11 110/92/A/43, New Tatachari colony,Shyamlal Buildings, Begum pet, Hyderabad91-9290910799)

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
Kishore Kumar.Madugula H.No: 1-11-110/92/A/43 New Tatachari colony Shyamlal Buildings

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

Posted 26-Apr-13 1:59am

ajayxlnc

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ajayxlnc · Answer 1 · 2013-04-26T02:17:00

Solution 2

Thanks For the reply.

Yes i can do it manually but i will be getting this kind of data twice in every week. So i have to create a generic solution for this kind of problem.

Can you help me out in finding out a solution. Is it possible.....!

Posted 26-Apr-13 2:17am

ajayxlnc

Comments

Dave Kreskowiak 26-Apr-13 9:52am

Re-read what I posted. THERE IS NO SOLUTION ON YOUR END!!

The data must be corrected on the source end, where it is input.

It is IMPOSSIBLE to come up with code that can read your mind and make the corrections. It has no way of knowing what a correct address should be. I told you how you rmight get closer, but it's not a fool-proof solution and will NOT fix every single addres with 100% accuracy. You will still need to look over every single record yourself.

Your ONLY solution is to have the source of the data correct all the problems on their end before it is sent to you.

Dave Kreskowiak · Answer 2 · 2013-04-26T02:13:00

If your data is missing a consistent seperator, there's no reliable way to do this. How is your code going to know that this:

123 Some Street SomeCity SomeStateSomPostalCode

Should be:

123 Some Street
SomeCity, SomeState  SomePostalCode

and not

123 Some Street SomeCity
SomeState  SomePostalCode

??

It can't. At least not without a TON of database work and complete lists of streets, cities, territories, postal codes, blah, blah, blah, ... som your code has something to draw from and recognize the malformed data.

Truthfully, with only 3500 records, it would be faster to go through them by hand and fix them all before importing them than it would to write the code to do it for you and test the crap out of it. You'd STILL have to go through and make sure that data is correct.

This is the problem with collecting data from the user and skilling the validation step at input time. The work you save them suddenly falls entirely on you to fix.