Hi, I'm trying to make my "secretarial" work a bit easier. We have
downloaded thousands of company profiles into our database. The company
name, company CEO, stock, etc, all managed to come into excel as
separate columns, thus easily workable information.

However, the address of the companies all came in one colum.

For example, these are all contained in their own cells.
*company
*micro
*stock
*msft
*money
*yes
*address
*1000 money way
suite money money
seattle, washington, $$$$$
USA
800-100-1000
800-100-1001

however, the address contains too much information

what I need to do is get all that address information into separate
columns, such as address 1, address 2 (for floor, suite, etc), city,
state zip code, phone number, fax number...

how do i go about doing this? do i need a macro that will enter the
cells? i'm not quite proficient enough in VBA to produce a macro that
will be able to target correctly what i need.

thanks


--
efang001
------------------------------------------------------------------------
efang001's Profile: http://www.excelforum.com/member.php...o&userid=31552
View this thread: http://www.excelforum.com/showthread...hreadid=512463

One common way is to use Data | Text-to-Columns and specify some
character which deliminates the different fields, eg comma. It is not
clear from what you have posted if a comma separates each part of the
address, and it can be awkward anyway as different addresses have a
differing number of fields. If you post some more examples then we may
be able to suggest ways of extracting part of the address at least (eg
phone/fax numbers).

Hope this helps.

Pete

Pete_UK Wrote:
One common way is to use Data | Text-to-Columns and specify some
character which deliminates the different fields, eg comma. It is not
clear from what you have posted if a comma separates each part of the
address, and it can be awkward anyway as different addresses have a
differing number of fields. If you post some more examples then we may
be able to suggest ways of extracting part of the address at least (eg
phone/fax numbers).

Hope this helps.

Pete

let me just add what i'm working with...
i believe that "most" of the telephone fields are the same number of
fields. Obviously the addresses are not, and this was a concern when I
posted here.

please see the attached document.


+-------------------------------------------------------------------+
|Filename: company list.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4361 |
+-------------------------------------------------------------------+

--
efang001
------------------------------------------------------------------------
efang001's Profile: http://www.excelforum.com/member.php...o&userid=31552
View this thread: http://www.excelforum.com/showthread...hreadid=512463

any help guys?


--
efang001
------------------------------------------------------------------------
efang001's Profile: http://www.excelforum.com/member.php...o&userid=31552
View this thread: http://www.excelforum.com/showthread...hreadid=512463

if i understand you correctly you have one collumn in your spreadsheet
which contains data you wish to split into 2 columns

what I have done in this situation is copy the offending column to
another spreadsheet. save as CSV file and open in notepad.

one opened in notepad you can use the "replace" option

replace " " with ","

save the sheet and open in excel and copy the 2 new columns back into
the orgional spreadsheet.

hope that helps


--
jmw
------------------------------------------------------------------------
jmw's Profile: http://www.excelforum.com/member.php...o&userid=31636
View this thread: http://www.excelforum.com/showthread...hreadid=512463

efang001 wrote...
....
let me just add what i'm working with...
i believe that "most" of the telephone fields are the same number of
fields. Obviously the addresses are not, and this was a concern when I
posted here.

please see the attached document.
....

Most sensible people never open files from strangers. If you want help,
put 2 or 3 records IN PLAIN TEXT into a follow-up. Generally, if there
are no clear patterns with which to distinguish address from other
fields, parsing becomes an unavoidably manual process.

efang001 Wrote:
Hi, I'm trying to make my "secretarial" work a bit easier. We have
downloaded thousands of company profiles into our database. The company
name, company CEO, stock, etc, all managed to come into excel as
separate columns, thus easily workable information.

However, the address of the companies all came in one colum.

For example, these are all contained in their own cells.
*company
*micro
*stock
*msft
*money
*yes
*address
*1000 money way
suite money money
seattle, washington, $$$$$
USA
800-100-1000
800-100-1001

however, the address contains too much information

what I need to do is get all that address information into separate
columns, such as address 1, address 2 (for floor, suite, etc), city,
state zip code, phone number, fax number...

how do i go about doing this? do i need a macro that will enter the
cells? i'm not quite proficient enough in VBA to produce a macro that
will be able to target correctly what i need.

thanks

Try Data, text to columns, delimited..............delimited will
separate data in one column to several columns


--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
View this thread: http://www.excelforum.com/showthread...hreadid=512463

Harlan Grove Wrote:
efang001 wrote...
....
let me just add what i'm working with...
i believe that "most" of the telephone fields are the same number of
fields. Obviously the addresses are not, and this was a concern when
I
posted here.

please see the attached document.
....

Most sensible people never open files from strangers. If you want
help,
put 2 or 3 records IN PLAIN TEXT into a follow-up. Generally, if there
are no clear patterns with which to distinguish address from other
fields, parsing becomes an unavoidably manual process.

that is definately true...but it is a zip file, you can view the
contents, and it is a excel file, and there are no macros.

i'm not a big virus/bug hunter, so, maybe i'm missing something here?

anyways, i'm trying some of these formats and advice, thanks


--
efang001
------------------------------------------------------------------------
efang001's Profile: http://www.excelforum.com/member.php...o&userid=31552
View this thread: http://www.excelforum.com/showthread...hreadid=512463