separating info in one column to different fields

[Guest]

I have a database of clients.

first column second column
company name contact name, address, city, zip

I need to separate the values in the second column

 

[Guest]

If you don't have to do it with VBA, you can simply use the Data -> Text to
Columns command, choosing delimited -> comma. It will then seperate the whole
column into four separate ones with the contact name, address, city and zip.

 

[Guest]

I recorded a macro doing it (as my VBA skills are limited) and it gave me:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/11/2006 by '
'

'
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1)),
TrailingMinusNumbers:=True
End Sub

 

[Guest]

I am sorry the second column does not have commas. I used them to separate
the words showing 3 distinct values that I wanted.

example:

first column second column
ABC CORPORATION JIM BOB 123 NEED HELP AVE CHICAGO 60630

No commas in the cell separating the fields I need.

 

[David]

You can set the delimited function to seperate on spaces.

-David

 

[Guest]

THEN Use spaces instead of commas as the delimiters. THis will give you more
than the 4 desired columns, but it's easy to merge them after wards usually.

 

[Guest]

How do I merge the ones I want back together after I separated the cell.

 

[Guest]

I separated on spaces but each row is different. Some have middle names. So
when I separated I now have a jumble of data. some of the names ran into the
address field now and so on. ALso how do I merge 2 colums into one.

 

[Guest]

whoever made this program should be shot

 

[NickHK]

Bryan,
Unless you can help Excel to determine the breaks, it looks like you will be
doing it manually.
To me, the easiest way to "help" is save the file as a .csv.
Then open it NotePad or other text editor
Go through the file adding commas where fields are split, adding an extra
comma if the field is missing.
Reopen in Excel.

If you a few hundred it's feasible. Thousands, good luck.

NickHK
P.S. Unless you have some way of controlling the output of the other program
in the first place to give consistent data.