Advanced Copy/Paste Techniques?

[efang001]

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

 

[Pete_UK]

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

 

[efang001]

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]

any help guys

 

[jmw]

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

 

[Harlan Grove]

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.

 

[JR573PUTT]

Hi, I'm trying to make my "secretarial" work a bit easier. We hav
downloaded thousands of company profiles into our database. The compan
name, company CEO, stock, etc, all managed to come into excel a
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 separat
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 th
cells? i'm not quite proficient enough in VBA to produce a macro tha
will be able to target correctly what i need.

thanks

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

 

[efang001]

efang001 wrote...
....
....

Most sensible people never open files from strangers. If you wan
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 th
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, thank