homepage Welcome to WebmasterWorld Guest from 23.23.12.202
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Hardware and OS Related Technologies / Website Technology Issues
Forum Library, Charter, Moderators: phranque

Website Technology Issues Forum

    
Getting Goverture Suggestions into Excel
And getting Excel tables into ascii text
Robert Charlton

WebmasterWorld Administrator robert_charlton us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 727 posted 7:15 am on Feb 2, 2002 (gmt 0)

This is probably more an Excel question than a Goverture question... To get clean results when pasting Goverture suggestions into an Excel (Windows version) spreadsheet, I've been going through an intermediate step of pasting them first into a text editor, then replacing the two spaces between the number and the phrase with a tab character, and then copying and pasting the result into Excel.

If I don't do it this way, I get html type link formatting on all the search phrases, and, when I eliminate this via Excel formatting, I get number cells two lines high with double-spaced rows on the phrases, etc... it's a mess to clean up.

I've also been trying to export or paste my Excel tables into an ascii text editor to give me an absolutely locked ascii txt document, with no shifting of columns due to different tab or font sizes. Any thoughts on accomplishing this? I use NoteTab Pro for editing text, but its Fixed Tab feature (converts tab characters to text) only seems to work with tabs created "live" within the program.

 

Woz

WebmasterWorld Senior Member woz us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 727 posted 7:36 am on Feb 2, 2002 (gmt 0)

What I do (Excel 97/2k)

1 only copy the actual results that appear Below the "Count Search Term" line
2 use Paste Special to past into Excel as text
3 use "data¦text to columns" command and select "fixed width"
Excel is then pretty good at selecting the correct position and removes any extraneous leading spaces in the result when you press OK.

This works pretty well if the range from Hi to Lo results is not too high, ie., Top result 1000, bottom result 100, but if the range is higher than that you may need to do a bit of fiddling.

Onya
Woz

Robert Charlton

WebmasterWorld Administrator robert_charlton us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 727 posted 8:55 am on Feb 2, 2002 (gmt 0)

Woz - Thanks... I'm also using Excel 97. Steps 1&2 work like a charm. Step 3 only seems to work one row at a time... No matter what I do to change the specified range, I end up affecting the first row only. What's the trick?

Woz

WebmasterWorld Senior Member woz us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 727 posted 9:05 am on Feb 2, 2002 (gmt 0)

Ah!

When you paste in step 2 everything you pasted should be selected. If not, make sure it is all selected and then do step 3.

Onya
Woz

Robert Charlton

WebmasterWorld Administrator robert_charlton us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 727 posted 9:32 am on Feb 2, 2002 (gmt 0)

With one cell or row selected, I'm given options to delete the column breaks I don't like. When everything that I've pasted is selected, I'm not given this choice.

And if the range is higher than 4 digits to 3 digits, the columns get really skewed. Can't figure how to "fiddle" with this, as you suggest.

Also, the only real delimiters that exist are the space characters, and Excel doesn't seem to be able to discriminate between 2 spaces (which is how I'm able to get my text editor to insert a tab) and 1 space (between each word), so I'm getting columns at every word break. Thoughts?

PS to above... I managed to get dataŠtext to columns to work by doing it in chunks with similar sized numbers. I think my old method may actually have been faster... ie, pasting it into a text editor, replacing the two spaces with a tab, and just pasting that into Excel.

Woz

WebmasterWorld Senior Member woz us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 727 posted 10:00 am on Feb 2, 2002 (gmt 0)

>Thoughts?
Yeah, come on over and I'll show you. What, too far? OK, well then,

>With one cell or row selected
Don't select single cells or rows. Select the range of cells you just pasted and make sure you only slecet a single column.

Lets say you have copied 10 term with numbers from Goverture. Set your cursor in cell A1 and Paste Special as I mentioned. You should then end up with Ten cells with information, all selected.

Then proceed with step 3.

>And if the range is higher than 4 digits to 3 digits, the columns get really skewed. Can't figure how to "fiddle" with this, as you suggest.
>Also, the only real delimiters that exist are the space characters

First, make sure you select "Fixed Width" when converting to more than one column otherwise the proceedure will attempt to parse at tabs, or commas, or whatever, and this is not what we want. By selecting Fixed Width the system is pretty good at figuring out where the next column should be by looking at the spaces and comparing where the next set of characters is.

There are always TWO spaces between the numbers and the words and this is what Excel uses to determine the columns. However, as you say, if the numbers of digits in the numbers is too varied, 1000000 vs 100, then it can get a little upset and split in the middle of some words.

Therefor the solution is to widen the gap between the numbers and words so the system can get a better idea of the boundaries. The easiest way is to simply replace double spaces with, say, ten spaces (cntrl H). Then do step 3.

See how you go with that.

Onya
Woz

Robert Charlton

WebmasterWorld Administrator robert_charlton us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 727 posted 6:48 pm on Feb 2, 2002 (gmt 0)

>>First, make sure you select "Fixed Width"...<<

That's the one thing I didn't try... Eyes get bleary late at night. Works like a charm. Many, many thanks.

>>Yeah, come on over and I'll show you. What, too far?<<

What's on the barbey? ;)

Now, does anyone have any idea how to export this beautiful spread sheet, to which I'll be adding other stats, to an ascii text editor with hard spaces rather than tab characters, so that everything stays aligned?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Hardware and OS Related Technologies / Website Technology Issues
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved