0
   

Question about Microsoft Excel, Please help!

 
 
Reply Fri 30 Mar, 2007 02:14 pm
I received a massive Text File of contacts that didn't seem to be delimited in any way. I don't want to input the text manually, because I know that's a waste of time. So far; I've manipulated the Data into a single Column (A), and the Data is consistently 17 rows long per contact... so here's the question:
How do I tell Excel to automatically convert:
A1 thru A17 to A1 thru Q1,
A18 thru A35 to A2 thru Q2,
A36 thru A53 to A3 thru Q3,
etc. thru the list of perhaps 300 or so contacts

Once this is done; I should be able to import it directly into my act database.

Does anyone know how to do this?
  • Topic Stats
  • Top Replies
  • Link to this Topic
Type: Discussion • Score: 0 • Views: 701 • Replies: 8
No top replies

 
OCCOM BILL
 
  1  
Reply Fri 30 Mar, 2007 03:12 pm
Error in question, sorry. 18 blanks per contact so:
a1-a18 to a1-r1,
a19-a36 to a2-r2,
a37-a54 to a3-r3
etc.
0 Replies
 
OCCOM BILL
 
  1  
Reply Fri 30 Mar, 2007 03:33 pm
Okay, I'm getting closer...
If I copy A1:A18 on sheet1
and then choose paste special-> transpose onto A1 on sheet2 it positions it correctly.
Next repeat the process A19:A36 sheet1, paste B1 sheet2 etc.

Can this be automated?
0 Replies
 
OCCOM BILL
 
  1  
Reply Fri 30 Mar, 2007 09:31 pm
I guess nobody knew how to do that. I did it manually 333 times. Mad

Easier question! How to I write: IF A1 is blank, copy B1 into it? I assume I can paste/special/justify the answer?
0 Replies
 
Butrflynet
 
  1  
Reply Sat 31 Mar, 2007 12:05 am
In Word Perfect we used to be able to add delimination to a bunch of words in a cell by specifying the first, second, third, fourth word and the delimiter to insert between them.

Don't recall ever being able to do that with Excel or Word. We may have done it using Access but it has been a very long time since I looked at it. I need to though, because I just got done doing the same thing you did, copy and pasting 200 names and emails manually into a spreadsheet because I couldn't remember how to automate it.

If you have Word Perfect, it should be a piece of cake. I know I did it with ease many times using that program years ago.
0 Replies
 
OCCOM BILL
 
  1  
Reply Sat 31 Mar, 2007 12:43 am
If the contact information for each contact is all in the same order, on the same line, Excel will let you add as many delimiting points as you wish. Unfortunately mine wasn't on one line. Bummer was that I could tell by the arrangement that it was sorted at some point. Confused If these contacts prove useful; I'll post a solution soon because I refuse to do this on a regular basis... and I know there's some simple solution. If nothing else; I'll write a macro that deletes the 18 lines after it transposes that data so it can start over in the same place. I'm not good at those, so I figured it would be a wash time-wise trying to perfect the macro or doing it manually. If I have to do it again, it won't be.
0 Replies
 
jespah
 
  1  
Reply Sat 31 Mar, 2007 04:35 am
OCCOM BILL wrote:
I guess nobody knew how to do that. I did it manually 333 times. Mad

Easier question! How to I write: IF A1 is blank, copy B1 into it? I assume I can paste/special/justify the answer?


=IF(A1="",B1,C1)

This is, if A1 is blank, copy B1. If not, copy C1. If you don't like the alternative, you can switch it to anything, even "" (which is blank). This is assuming, by the way, that these are true blanks and are not cels in which the space bar has been hit.

As for the formatting, do it after the copying is all completed. It would help, though, to define the area so that you don't end up missing some cels or adding ones you don't need. Just select everything you want to do this to and go to Insert>Name>Define and name the range something. That's for that bar called Names in workbook. Then hit OK. To get to that named range (you can name it anything with letters, I can't recall if numbers are allowed; special characters like asterisks probably aren't allowed, either), go to the name box in the upper left corner of the sheet. It is found just above cel A1 and just to the left of the formula bar.

Sorry I wasn't around to help you with the other part but I think you may have done all you could do with Excel (sometimes, you just gotta hit things with a hammer, know what I mean?).
0 Replies
 
OCCOM BILL
 
  1  
Reply Sat 31 Mar, 2007 05:30 am
You rock Jespah! Thanks. It took me a spell to figure out I needed a new column to not make a circular argument (Embarrassed), but it's working now. The contact list begins company, name, etc, but who ever put it together put the name in the company slot for anyone who didn't have a company. Fixed, thanks. If I am forced to come up with a solution to the initial dilemma, I'll post it.
0 Replies
 
jespah
 
  1  
Reply Sat 31 Mar, 2007 07:38 pm
Hey, I'm glad it worked. Smile Thanks! Embarrassed
0 Replies
 
 

Related Topics

Clone of Micosoft Office - Question by Advocate
Do You Turn Off Your Computer at Night? - Discussion by Phoenix32890
The "Death" of the Computer Mouse - Discussion by Phoenix32890
Windows 10... - Discussion by Region Philbis
Surface Pro 3: What do you think? - Question by neologist
Windows 8 tips thread - Discussion by Wilso
GOOGLE CHROME - Question by Setanta
.Net and Firefox... - Discussion by gungasnake
Hacking a computer and remote access - Discussion by trying2learn
 
  1. Forums
  2. » Question about Microsoft Excel, Please help!
Copyright © 2022 MadLab, LLC :: Terms of Service :: Privacy Policy :: Page generated in 0.04 seconds on 07/01/2022 at 11:17:58