software

Hi, I'm trying to make my quot;secretarialquot; 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: www.excelforum.com/member.php...oamp;userid=31552
View this thread: www.excelforum.com/showthread...hreadid=512463One 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:
gt; One common way is to use Data | Text-to-Columns and specify some
gt; character which deliminates the different fields, eg comma. It is not
gt; clear from what you have posted if a comma separates each part of the
gt; address, and it can be awkward anyway as different addresses have a
gt; differing number of fields. If you post some more examples then we may
gt; be able to suggest ways of extracting part of the address at least (eg
gt; phone/fax numbers).
gt;
gt; Hope this helps.
gt;
gt; Pete

let me just add what i'm working with...
i believe that quot;mostquot; 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: www.excelforum.com/attachment.php?postid=4361 |
-------------------------------------------------------------------

--
efang001
------------------------------------------------------------------------
efang001's Profile: www.excelforum.com/member.php...oamp;userid=31552
View this thread: www.excelforum.com/showthread...hreadid=512463
any help guys?--
efang001
------------------------------------------------------------------------
efang001's Profile: www.excelforum.com/member.php...oamp;userid=31552
View this thread: 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 quot;replacequot; option

replace quot; quot; with quot;,quot;

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: www.excelforum.com/member.php...oamp;userid=31636
View this thread: www.excelforum.com/showthread...hreadid=512463efang001 wrote...
....
gt;let me just add what i'm working with...
gt;i believe that quot;mostquot; of the telephone fields are the same number of
gt;fields. Obviously the addresses are not, and this was a concern when I
gt;posted here.
gt;
gt;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:
gt; Hi, I'm trying to make my quot;secretarialquot; work a bit easier. We have
gt; downloaded thousands of company profiles into our database. The company
gt; name, company CEO, stock, etc, all managed to come into excel as
gt; separate columns, thus easily workable information.
gt;
gt; However, the address of the companies all came in one colum.
gt;
gt; For example, these are all contained in their own cells.
gt; *company
gt; *micro
gt; *stock
gt; *msft
gt; *money
gt; *yes
gt; *address
gt; *1000 money way
gt; suite money money
gt; seattle, washington, $$$$$
gt; USA
gt; 800-100-1000
gt; 800-100-1001
gt;
gt; however, the address contains too much information
gt;
gt; what I need to do is get all that address information into separate
gt; columns, such as address 1, address 2 (for floor, suite, etc), city,
gt; state zip code, phone number, fax number...
gt;
gt; how do i go about doing this? do i need a macro that will enter the
gt; cells? i'm not quite proficient enough in VBA to produce a macro that
gt; will be able to target correctly what i need.
gt;
gt; thanks

Try Data, text to columns, delimited..............delimited will
separate data in one column to several columns--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=512463
Harlan Grove Wrote:
gt; efang001 wrote...
gt; ....
gt; gt;let me just add what i'm working with...
gt; gt;i believe that quot;mostquot; of the telephone fields are the same number of
gt; gt;fields. Obviously the addresses are not, and this was a concern when
gt; I
gt; gt;posted here.
gt; gt;
gt; gt;please see the attached document.
gt; ....
gt;
gt; Most sensible people never open files from strangers. If you want
gt; help,
gt; put 2 or 3 records IN PLAIN TEXT into a follow-up. Generally, if there
gt; are no clear patterns with which to distinguish address from other
gt; 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: www.excelforum.com/member.php...oamp;userid=31552
View this thread: www.excelforum.com/showthread...hreadid=512463