Solved: Excel help with delimited types

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

agpilot

Thread Starter
Joined
Dec 25, 2004
Messages
95
Hello all:
I have just one file I need help with. This file has symbols for 3800 stocks. Each line has only one stock symbol. My charting program will not accept Importing these symbols unless it has .asc or .txt extensions. It also must be in comma, space or tab delimited. That's my hangup. I hardly ever work with or understand comma, space or tab delimited.

All I understand so far is that when I am on any line and I hit the "end" key the cursor will move several spaces beyond the the last letter of the symbol.
All these stock symbols are either one, two, three or four letters yet there seems to be about 10 spaces between the first letter and where the cursor stops when the "end" key is hit.

How do I get rid of all those "blank" spaces on each line so the curson stops right after the last letter of each stock symbol when I hit "end" key??

Could this be done in MS Wordpad? I have Excel ver 7 (Yes it's old but hardly ever used) I kinda hate doing this by hand for 3800 lines. Any quick and easy way for a person who almost never gets into this "delimiting" stuff?
Thanks for suggestions. agpilot Win 98SE, Excel v7
Edit: I forgot to mention that the orignal file was .xls and I deleted the stock company names and saved just the symbols on each line as that's all I want.
 
Joined
Aug 17, 2003
Messages
17,584
Lets assume the original data starts in cell A1.
In cell A2, insert the formula =TRIM(A1)

That should strip all leading and trailing spaces and give a cleansed copy of A1 entry in A2.
Copy the formula down to the last row (about row 3800 by the sound of it) and then copy the entire row, and edit - paste special - values to another sheet or wherever you need them, which will give just the bare values without the spaces.
 

agpilot

Thread Starter
Joined
Dec 25, 2004
Messages
95
Hello Kiwiguy

Thanks but I guess I'll have to bow my head and state that I really don't know much about using even this old version of Excel. I am not even sure where cell A2 is. I asked if Ms Wordpad might be of some help hopeing for the most basic way of doing this. But.. hey I'll open up the help files and look for A2.. By the way, it's getting time for sleep here so I'll be back in 8 hours. Gosh, there always seems to be another software to learn every time I run into a snag.. Thanks Kiwiguy..........
Agpilot
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
If it's just symbols, and you just need to get rid of the spaces, how about opening it in Word? Then hit Ctrl+H.

Put a space in the Find what box (you won't SEE the space). Don't put anything in the Replace With box, and hit Replace all.
 

agpilot

Thread Starter
Joined
Dec 25, 2004
Messages
95
Hi Dreamboat
Well, I'll give an example:
Line 1: AA
Line 2: AACB
Line 3: AACE
Line 4: AAI
Line 5: AAP
and so down to line 3800..

The "spaces" are right after the last visable letter in each line.

I hope I explained this fairly clear... An expert would do this job in a half minute.. I think I've found where Kiwiguy wants =TRIM(A1) but I am not sure how to "run" that comand..
I have to go to sleep soon so untill morning... Thanks everyone. Agpilot
 

agpilot

Thread Starter
Joined
Dec 25, 2004
Messages
95
Dreamboat said:
If it's just symbols, and you just need to get rid of the spaces, how about opening it in Word? Then hit Ctrl+H.

Put a space in the Find what box (you won't SEE the space). Don't put anything in the Replace With box, and hit Replace all.
---------------------------

Hi Dreamboat
Well you sure made my day with your suggestion.. It worked in Word. A friend had Word '97 on an old computert.

It deleted 25,410 spaces. Am I glad I didn't try deleting those by hand one at a time.. Thank you very much. Hope you have a nice 4th of July.
agpilot
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Cool. Though after thinking about it, you could have selected the column, gone to Data-->Text to columns, choose space as the delimiter, and choose the thingee that makes all spaces one delimiter. But ... whatever works! :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top