EXCEL and a Pasted Word Table

[Denis Brennan]

I have a WORD table that I want to copy to EXCEL to use the enhanced
sort features. The table looks like this:

abcdefgh 325 (2) (a) (ii)
abcdefgh 325 (1) (a) (ii)
abcdefgh 325 (2) (a) (i)

Before pasting into EXCEL, all but the second column are formatted as
Text. hen, i select the table in WORD and paste into EXCEL. After the
paste, the entry in the third column becomes -2. I know that this is
becasue EXCEL treats this as a negative number. However, I want it to
be treated as (2), because (a) I need EXCEL to sort it (ascending) as a
text field so that I get
(1)
(2)
(3)

and not
-3
-2
-1

and (b) because, eventually I will paste the sorted table back to WORD
and I need to retain (1).

I have been advised to change my International settings. I have done
this but it has no effect. I have tried a custome format, but this does
not work as it still treats the underlying entry as numeric, ruining
the Sort.

Can anyone help?

Many thanks,

Denis

 

[JulieD]

Hi Denis

do you do a copy in word & then a paste in excel?
if so what do you do after the paste? or do they just all go into their own
cells without any further intervention?

let me know as i have an idea.

Cheers
julieD

 

[Dave Peterson]

If you treat the value as a negative number and format it to show ()'s, then
sort it in descending order, didn't it work ok?

http://groups.google.com/[email protected]

 

[Andy Brown]

After the

paste, the entry in the third column becomes -2.

Yes. Perhaps you could (find &) replace "(" with "((" in the relevant column
(in the Word table, before you copy and paste in). Then remove the extra "("
once you've pasted back into Word.

HTH,
Andy

 

[Denis Brennan]

Hi Denis

do you do a copy in word & then a paste in excel?
if so what do you do after the paste? or do they just all go into their own
cells without any further intervention?

let me know as i have an idea.

Cheers
julieD

Hi, Julie,

I copy in Word then I paste in EXCEL. After that I sort in EXCEL, copy
back to Word and, finally, convert table to text, using tabs. Thanks
for any suggestions.

DEnis

 

[Denis Brennan]

If you treat the value as a negative number and format it to show ()'s, then
sort it in descending order, didn't it work ok?

http://groups.google.com/[email protected]

Dave,

I'm sorry. I lost the thread earlier.

OK! My problem is that I must be able to sot in ASCENDING order; first
by ertain columns then by others. See my example above to understand
why. The numberin brackets is a "subsection number" and therefore must
sort (1), (2), (3) etc...

Thanks,

Denis

 

[Dave Peterson]

If you sort -1,-2,-3,-4 in ascending order, you'll get -4,-3,-2,-1.

But if you sort by that column in descending order, you'll get
-1,-2,-3,-4

And using a custom format of: General;(0)

It'll look like: (1),(2),(3),(4) after a descending sort.

You can sort each field independently.

I guess my thought is that I don't care how I sort it (ascending or descending),
but I do care how it looks in the worksheet (and what copies back to MSWord).

 

[JulieD]

Hi Denis

i'm a bit confused as this thread seems to be all over the place - have you
got a solution yet?

When i pasted it into Excel it went into one cell and i had to use data /
text to columns to break it up .. as part of the process i could nominate
the (1) columns as text so it worked fine.

From what i'm understanding this isn't happening with you, the data is going
into individual cells and "auto" converting in the process.

So an alternative is that you convert it from a table in word to a comma
separated list and then do the copy & paste - it might not then just split
itself up (or don't use , use something odd like @ or # or " and then use
Data / Text to Columns to split it up).

Hope this helps.

Cheers
JulieD



my only other suggestion is that in word you convert it from a table