awful table

  • Thread starter Thread starter Marie Lavoie
  • Start date Start date
M

Marie Lavoie

I have a co-worker who is keeping stats about our files.
She keeps it in wordperfect, in a BIG table (a line for every week since
1995).
Now, my boss wants me to put this table in excel, so the total can be made
and that it opens faster, etc...
The thing is:
At one point, she starts keeping the number of a kind of file in same cell.
Ex:
A B C
181(30) 220(18) 220(22)
171(31) 218(17) 221(22)
....
Now, i'm trying to but the number in the parenthesis in an adjecent cell in
excel, so it looks like:
A * B * C *
181 30 220 18 220 22
....

How am i suppose to copy the table?
When i do copy/paste, it puts everything in one column, thinking thay the
second number is negatif, but puts it on a different line:
181
-30
220
-18
220
-22

Someone has an idea?

Marie
 
Hi Marie

This is absolutely not tested, theory only.

First make room for the data; insert new empty columns so you have a blank
B, D and F column.
Then select A column. Do Data > Text to columns and enter ( as delimiter.
Repeat action for C and E columns. Finally edit-replace ) with nothing.

If this is something you have to do over and over, then
1) test and adjust until the routine works;
2) record a macro while doing it, so you can just run it from now on.

HTH. Best wishes Harald
 
hum... I've imported data before, but I'm having difficulties to copy
instead and do that. Have seen nothing in "special paste". Trying to see
where i could do that. Also, i see no text menu under Data... (doesn't help,
i have a french version, but still, i should be able to see something
meaning it.) How would you do it? Would you paste directly in the form i
want or do you paste, then do a modification?
Your idea seems pretty brilliant, but i'm having some problem finding what
i'll use to do it...

Marie
 
I haven't used WordPerfect in 15 years, but if you're having trouble copying and
pasting into excel, maybe you could do, er, try this (also untested!):

I did a file|open in MSWord. It looks like MSWord will open the wordperfect
files.

Maybe you could open the file in Word and copy|paste from there.

If it gets brought into Word as a table, maybe a simple copy from there and
pasting into Excel will work. (or maybe not????)


If that doesn't work, maybe you could run a couple of formulas to take that
single column in excel and break it into 6 columns.

With your data in A1:Axxx, put this in B1:
=OFFSET($A$1,(ROW()-1)*6+COLUMN()-2,0)

Drag across to G1 (6 columns total)
and drag down until you run out of data.

And to make things all positive:
=abs(OFFSET($A$1,(ROW()-1)*6+COLUMN()-2,0))

Copy that range|paste special values and kill that first column.
 
I know copying won't work. I tried copying from WordPerfect to QuattroPro
(Corel's equivalent of excel) And same result then excel. and when i copy
any table from WP to Word, they always add me some "return" at beginning of
all cells.

But for you idea, i first want to understand it. Because i don't have 6
columns. I have more and other columns too, with subtotal, etc. But for
those who look like the ones i showed you, i was thinking of copying column
by column. Because when i copy in one time, it doesn't show in one line.
Actually, i haven't understand the pattern yet... it AWFUL. So if i
understand it, i could modify it and keep me of asking an other question
latter when the aswer will use those functions.

A little bit more help and I should be ok. :-)
Thank you.

Marie
 
I didn't see a question in here <bg>, so post back when/if you find the pattern
and need help. I'm sure that someone can help.
 
Marie Lavoie said:
I have a co-worker who is keeping stats about our files. [...]
At one point, she starts keeping the number of a kind of file in same cell.
Ex:
A B C
181(30) 220(18) 220(22)
171(31) 218(17) 221(22)
Click to expand...


Keeping in mind that I haven't used WordPerfect since the 1980's,
couldn't you do some cleanup in WordPerfect before you try to put the
data into Excel? If the data were in Word, I would do something like
the following:

1. In Word, convert the table to text, using a tab character as the
delimiter.
2. Search and replace every '(' character with a tab character. (If
there are parentheses elsewhere that shouldn't be replaced, the search
string would have to get creative, but it's still doable.)
3. Search and replace every ')' character with nothing. (Again, if not
all parantheses are bad, the search string would need to get more
creative.)
4. Convert the tab-delimited text back into a table. Obviously, you'll
now have three more columns than you started with.
5. Now attempt the copy/paste (or export or whatever) into Excel.

I have no idea if WordPerfect has the search & replace capabilities to
enable you to do this, but if yes, then this should eliminate some of
the problems you're having.
 
I tried doing the job in WP already, but i was trying to find and replace
something else so the data would chance column. And yes, WP has those
capabilities and even more! ;-) (yup, i'm a Corel WP preacher, sorry!)
So thank you, didn't think of doing it this way.

Marie

Martha said:
Marie Lavoie said:
I have a co-worker who is keeping stats about our files. [...]
At one point, she starts keeping the number of a kind of file in same cell.
Ex:
A B C
181(30) 220(18) 220(22)
171(31) 218(17) 221(22)
Click to expand...


Keeping in mind that I haven't used WordPerfect since the 1980's,
couldn't you do some cleanup in WordPerfect before you try to put the
data into Excel? If the data were in Word, I would do something like
the following:

1. In Word, convert the table to text, using a tab character as the
delimiter.
2. Search and replace every '(' character with a tab character. (If
there are parentheses elsewhere that shouldn't be replaced, the search
string would have to get creative, but it's still doable.)
3. Search and replace every ')' character with nothing. (Again, if not
all parantheses are bad, the search string would need to get more
creative.)
4. Convert the tab-delimited text back into a table. Obviously, you'll
now have three more columns than you started with.
5. Now attempt the copy/paste (or export or whatever) into Excel.

I have no idea if WordPerfect has the search & replace capabilities to
enable you to do this, but if yes, then this should eliminate some of
the problems you're having.
Click to expand...
 
Back
Top