Moving text to other columns (1 Viewer)

[bdhtexas]

I am making a mailing list in Excel to send letters advertising my new business. I copied and pasted data from Yahoo Business Listings onto an Excel Spreadsheet.

The problem is this, I now have several hundreds of lines of data and I need to move certain data over to the correct columns.

A simple text to columns doesn't work here because data is not formatted to do it quickly.

Example:
A1 - Business Name B1 - Phone C1 - Address D1 - City
A2 - ABC Company
A3 - (702) 555-3100 1200 S Main St
A4 - Houston
A2 and every 3rd row down contains the Business Name
A3 and every 3rd row down contains both the phone number and address
A4 and every 3rd row down contains the city
I don't need to move A2
I need to move A3 to B3 and C3
I need to move A4 to D4

Then I can run a macro to eliminate the empty cells, I already have the code.

My question - is there a simple way to move this data or do I need to go in and format it so I can use text to columns?

Any help is appreciated.

Thanks!

 

[unmarkedhelicopter]

You want help with spam ???????

 

[jclu]

@unmarkedhelicopter: What bdhtexas is doing isn't spam! Yahoo Business Listings is a public listing, so how is it spam? Do most of us not do an equivalent when looking for a job, and we 'cold-call'? Ask any career resource center staff about how to cold-call when job-searching, and they'll tell you to look up businesses either in the Yellow Pages or on Workopolis or Monster, and get the phone number and call the place. All bdhtexas wants to do is format the data in Excel so it's easier to work with.

@bdhtexas: If you still need help, there's no real simple way to do it afaics apart from running a macro. You mentioned you already have the code, but I'm not sure if it's code to remove the empty cells or do the formatting as well. Assuming it's just the empty cells, then you're better off creating a macro to format the data once it's imported/pasted. You could go through and manually format the data, but it's time consuming and personally I think it's faster and a better use of your time to create a macro . Assuming you have some knowledge/experience with creating macros using VB, the logic would look something like this:

1) Paste the data (manually I suppose)
2) Copy the header row over to a new sheet
3) From A2 down, copy A2 to the new sheet column A
4) Copy the phone number part of A3 to the new sheet column B
5) Copy the address part of A3 to the new sheet column C
6) Copy A4 to the new sheet column D
7) Repeat from A5 onward

HTH

 

[unmarkedhelicopter]

@unmarkedhelicopter: What bdhtexas is doing isn't spam! Yahoo Business Listings is a public listing, so how is it spam? Do most of us not do an equivalent when looking for a job, and we 'cold-call'? Ask any career resource center staff about how to cold-call when job-searching, and they'll tell you to look up businesses either in the Yellow Pages or on Workopolis or Monster, and get the phone number and call the place. All bdhtexas wants to do is format the data in Excel so it's easier to work with.

You seem to have a different view of 'what spam is' than I do.

Spam is 'unsolicited' 'advertising' 'email' generated by some 'automatic' or 'semi-automatic' means. I think this falls into this catagory.

I'd 'love' to hear why you 'think' it's not

 

[bigclumber]

Spam is 'unsolicited' 'advertising' 'email'...

His datasheet does not include an e-mail address. I'm assuming this data set is for a bulk postal mailing.

 

[unmarkedhelicopter]

Ah ! So that makes it alright then ?????

 

[jclu]

Spam is 'unsolicited' 'advertising' 'email' generated by some 'automatic' or 'semi-automatic' means.

If that's the case, and it includes (snail) mail, then credit card and line of credit offers that many times are unsolicited should be considered spam as well. You don't hear people complaining, saying "let's work toward stopping companies from sending these offers." There has to be a line between allowing for some unsolicited advertising, as word-of-mouth isn't always good enough, and not allowing companies to flood consumer's mailboxes/inboxes with such advertising. Heck, I've even had my cell company allow unsolicited advertising voicemail to be left on my phone; all of a sudden I'll get the beep that I have a voicemail, and not indication that I missed a call, and when I check the voicemail, it's an ad, and many times the voice in the ad is the 'narrator' voice my cell company uses on its tv commercials!

So if you're going to denounce attempts by someone to advertise to businesses their new product, then go all the way and denounce all forms of unsolicited advertising, regardless of medium. Otherwise, to me you're just a hypocrite only focusing on one medium while ignoring the rest.

 

[unmarkedhelicopter]

If that's the case, and it includes (snail) mail, then credit card and line of credit offers that many times are unsolicited should be considered spam as well. You don't hear people complaining, saying "let's work toward stopping companies from sending these offers." There has to be a line between allowing for some unsolicited advertising, as word-of-mouth isn't always good enough, and not allowing companies to flood consumer's mailboxes/inboxes with such advertising. Heck, I've even had my cell company allow unsolicited advertising voicemail to be left on my phone; all of a sudden I'll get the beep that I have a voicemail, and not indication that I missed a call, and when I check the voicemail, it's an ad, and many times the voice in the ad is the 'narrator' voice my cell company uses on its tv commercials!

So if you're going to denounce attempts by someone to advertise to businesses their new product, then go all the way and denounce all forms of unsolicited advertising, regardless of medium. Otherwise, to me you're just a hypocrite only focusing on one medium while ignoring the rest.

You are quite right I am a hypocrite ! But I do subscribe to services which block spam ... I "should not" get "telephone calls" making "offers", I "should not" get "emails" making "offers" and I "should not" get "snail mail" making "offers" I abhor ALL of them. (unfortunately 'some' crap still gets through. I bet the guy above does not subcribe to a list clearing agency for his shots ! hence my position). So in that sense I am NOT a hypocrite.

By the way when I said "Ah ! So that makes it all right then ???????" that uses a device known as "sarcasm !" look it up in der book with all the big wurdz init an u mite get zum unerstanding ...