how do you remove leading spaces etc in cells?

[Guest]

I am trying to create a book listing my record/karaoke collection in
Excel, from the file names on my hard drive.

So far, I went to the command prompt in DOS, did a DIR to txt file
listing.

Have now manipulated this "txt" data into disk number, artist name and
track name in columns in Excel, but want to clean up the individual
cells. Here is what I would like to achieve.

1. A lot of the cells have a treble quotation leading in front on the
artists name for example, it would be nice if I could remove these
automatically (there are thousands).

2. Also a lot of leading spaces

3 If possible I would like to remove the trailing ".zip" ending from
the cells, again automatically

Please could someone help me thank you.

Mark.

 

[Brad]

Using the find/replace all feature of Excel (Excel 2007 labels this
"Find&Select")

Just don't enter anything in the "replace with" line and that will get rid
of the trebles and the .zip - make sure that you select the appropriate range
(or the entire worksheet)

If you use the =trim() function that will get rid of leading spaces.....

 

[Guest]

Using the find/replace all feature of Excel (Excel 2007 labels this
"Find&Select")

Just don't enter anything in the "replace with" line and that will get rid
of the trebles and the .zip - make sure that you select the appropriate range
(or the entire worksheet)

If you use the =trim() function that will get rid of leading spaces.....

Thanks Brad, that was brilliant, I have used the replace command and
its done a great job. Used it for some other cleaning operations, the
Database is starting to look great.

Can't though, get the hang of the =trim() command.
Is there any sites you could point me towards, that explain it in a
bit more detail, I tried using the Help option, but I am too thick to
understand what they are trying to tell me.

Is there an option to remove numbers only, some of the record titles
have the track number proceding them, I don't mind if I lose the odd
track name, that has a valid number at the begining of its title?

Lastly is there an option to Capatalise all the first letters of words
in a cell?

By the way started this job some 7 hours ago, nearly there now, good
job I am on holiday this week.

I am using Excel 2002

Mark.

 

[Brad]

assume cell c4 has " alan parsons project"

in cell d4 =trim(c4) would remove the first spaces

At the same time

=proper(trim(c4)) would yield Alan Parsons Project - without the leading
spaces...

If you don't have numbers in your titles - you could simply find and replace
0 with nothing, then find and replace 1 with nothing ... find and replace 9
with nothing. If you have numbers in your titles - this method will get rid
of information you want to keep.

if you have numbers in your titles - is there something right afterthe track
number (like a "."? if so you can find and replace 0. with nothing .....

 

[Guest]

assume cell c4 has " alan parsons project"

in cell d4 =trim(c4) would remove the first spaces

At the same time

=proper(trim(c4)) would yield Alan Parsons Project - without the leading
spaces...

If you don't have numbers in your titles - you could simply find and replace
0 with nothing, then find and replace 1 with nothing ... find and replace 9
with nothing. If you have numbers in your titles - this method will get rid
of information you want to keep.

if you have numbers in your titles - is there something right afterthe track
number (like a "."? if so you can find and replace 0. with nothing .....

Still not got it yet, will work on it over the weekend, on where I am
supposed to put the commands you say.

Don't know how to thank you for your help, so here is a risky
south-london joke (the best I know at the moment) as some sort of
payment. Hope you like it, it reminds me of the times when I worked
for some bigger companies, where so complete twats took the credit for
the company doing well.









Some years ago, Stan married an attractive woman, Marilyn, half his
age, in a small Cornish community. After several months, young Marilyn
complained that she had never climaxed during sex and according to her
Grandmother, all Cornish women are entitled to a climax once in a
while. So, to resolve the problem, they went to see the Veterinarian
since there was no trustworthy doctor anywhere in Truro.

The Vet didn't have a clue, but he did recall how, during the hot
summer, a farmer, would fan a cow that was having difficulty breeding,
with a big towel. This would cool her down and make her relax. So the
Vet told them to hire a strong, virile, young man to wave a big towel
over them while they were having sex. This, the Vet said, would cause
the young wife to cool down, relax, then climax. So the couple hired a
strong young man from Newquay to wave that big towel over them as the
Vet suggested. After many efforts, Marilyn still had not climaxed so
they went back to the Vet.
The Vet said for Marilyn to change partners and let the young man have
sex with her while Stan waved the big towel. They tried it that night
and Marilyn went into wild, screaming, ear-splitting climaxes, one
right after the other for about two and a half hours. When it was
over, Stan looked down at the exhausted young man and in a boasting
voice said:

scroll down.......




































'And that, my son, is how you wave a towel!'






Let me know if you found that funny.

Mark in Spain.

 

[Guest]

assume cell c4 has " alan parsons project"

in cell d4 =trim(c4) would remove the first spaces

At the same time

=proper(trim(c4)) would yield Alan Parsons Project - without the leading
spaces...

If you don't have numbers in your titles - you could simply find and replace
0 with nothing, then find and replace 1 with nothing ... find and replace 9
with nothing. If you have numbers in your titles - this method will get rid
of information you want to keep.

if you have numbers in your titles - is there something right afterthe track
number (like a "."? if so you can find and replace 0. with nothing .....

Sorry Brad, but still struggling.

I assume you mean insert a new column next to the one I am trying to
edit (Column C), and place results in this column (Column D).
Using the Paste Function button
Under Function Catagory I select "Text"
Under Function Name I select "Trim" for example
In the Trim Box, I type the cell number of the text I am trying to
edit (C4 for example)
In the "Formula Results =" box, it shows the text corectly edited, ie
the spaces removed, but when OK'ing this, the result in D4 shown on
the spreadsheet, is the formula (=trim(C4), not the result.
Have tried formatting the cells columns to text and to general with no
luck have also unlocked the columns with no luck (even though no
password is set-up for the spreadsheet).

Please could you help me further?

 

[Gord Dibben]

If you see the formula and not the result it would mean that the cell is
formatted as Text.

Format to General and reenter the formula by F2 then ENTER.

=TRIM(C4)


Gord Dibben MS Excel MVP

 

[Guest]

If you see the formula and not the result it would mean that the cell is
formatted as Text.

Format to General and reenter the formula by F2 then ENTER.

=TRIM(C4)


Gord Dibben MS Excel MVP

Sorry, but already tried formatting to "General".

 

[Gord Dibben]

Please top-post when responding to one of my posts.

Did you re-enter the formula after formatting to General?

CTRL + `(backquote)..................maybe you are in "formula view"


Gord

 

[Guest]

Had to leave this problem for a while, have come back to it with a
fresh brain. Have sorted the problem of the formula's, not sure what
the problem was with the sheet I was working on, it just rufused to
show the result of formulas. Opened a clean new sheet, pasted in my
data, set the formulas up, and now they work a treat, thanks Brad and
Gord.

Just need a bit more help please. I now have new columns showing the
results of the formulas, using the Proper and Trim commands, what I
would like to do now is lose the original UN Trimmed Un Proprered
columns, but as soon as I delete them, the results columns data
disappears as well, is there a way to lock the results column's?
TIA

 

[Gord Dibben]

Copy then, in place, Edit>Paste Special>Values>OK>Esc.

Now delete the source data.


Gord

 

[Guest]

Thank you very much, it worked just great.

Mark in Spain.

 

[smartin]

Anti-Spam wrote:
[snips]

Don't know how to thank you for your help, so here is a risky
south-london joke (the best I know at the moment) as some sort of
payment. Hope you like it, it reminds me of the times when I worked
for some bigger companies, where so complete twats took the credit for
the company doing well.

What, no takers?

Well it made me chuckle. Thanks for the entertainment.

[snipped!]