separating data in one coloum to multiple coloumns

**manwinderjit** · 07-26-2010, 04:30 AM

Hie i have some data and currently i'm trying all the excel formulas even from this website but the data seems don't get to sort accordingly to what i want below is the data which consists of address, city , postcode , state ,phone number 1 ,phone number 2 & phone number 3

Example below:

PEJABAT KESIHATAN MARUDI KESIHATAN ALAM SEKITAR D/A HOSPITAL MARUDI 98050 MARUDI SAR 085756249 085755511

(all the above data is in 1 coloumn and i want to break it into multiple coloumn according to the above criteria)

Address - PEJABAT KESIHATAN MARUDI KESIHATAN ALAM SEKITAR D/A
HOSPITAL MARUDI
Postcode - 98050
City - MARUDI
State - SAR
Phone Number 1 - 085756249
Phone Number 2 - 085755511

I have tried several trick displayed in Mr. Excel website but it still doesn't works

can any 1 assist me on this...

**Andrew-R** · 07-26-2010, 05:00 AM

You've set yourself a challange there, and no mistake.

From a quick look at your example sheet there is no consistent format. You could write a macro to parse some of it, but the chances are that it would be so complex and need to handle so many exceptions that you'd be better off manually sorting the data.

**manwinderjit** · 07-26-2010, 06:42 AM

i have almost 26 milion of data .....such as this which needs to be sorted out...how to do it...?

**Andrew-R** · 07-26-2010, 07:01 AM

The only way I can see to do it is:

1. Loop through every row in the spreadsheet.

2. Use Split with space as a delimiter to break the row down into individual words.

3. Use UBound with a For...Next loop to go through each element in the array.

4. Using IsNumeric check to see if the entry is a number, then check to see if it's 5 digits, if it is then assume that this is your postcode.

5. If you haven't yet found the postcode then concatenate the strings to give you the basic address

6. Immediately after the postcode are the city and then the state. However, some of these have spaces in, so you need additional logic to decide which is city and which is state. I'd suggest that you do this by having a data sheet with a list of all of the possible states on, so you can lookup valid states and assume that any text which isn't part of a valid state name is the city name.

7. Assume that anything after the state is a telephone number, and create an array to hold the telephone numbers seperately. You'll might want to do some validation to strip out non-numeric strings. My experience of dealing with volumes of data of this type is that you'll find that some have spaces in, which means you'll need logic to check that each value is actually long enough to be a telephone number and, if it's not, then concatenate it with the following number.

8. Output all of the data to somewhere useful, such as another sheet.

I've done similar things for contact lists in the past and the above will work in general, but you *will* end up performing tweak after tweak when you find rows that don't exactly conform with your pattern.

If you know enough about VBA to write the script I've described above then you might manage it, if you don't then you'll have to employ somebody who does or do it all manually. Some kind soul here might feel inclined to write the code for you, but it will be a nightmare of never ending corrections, so I'm going to pass, sorry. Hope I've pointed you in the right direction.