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.

[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 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

[efang001]

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