separating info in one column to different fields
 

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

separating info in one column to different fields
 

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.

"bryan" wrote:

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

separating info in one column to different fields
 

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


"bryan" wrote:

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

separating info in one column to different fields
 

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.

"bryan" wrote:

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

separating info in one column to different fields
 

You can set the delimited function to seperate on spaces.

-David
bryan wrote:
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.

"bryan" wrote:

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

separating info in one column to different fields
 

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.

"bryan" wrote:

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.

"bryan" wrote:

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

separating info in one column to different fields
 

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



"davidgareau" wrote:

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.

"bryan" wrote:

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.

"bryan" wrote:

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

separating info in one column to different fields
 

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.

"David" wrote:

You can set the delimited function to seperate on spaces.

-David
bryan wrote:
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.

"bryan" wrote:

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

separating info in one column to different fields
 

whoever made this program should be shot

"bryan" wrote:

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

separating info in one column to different fields
 

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.


"bryan" wrote in message
...
whoever made this program should be shot

"bryan" wrote:

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