Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
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..... -- Wag more, bark less "Anti-Spam" wrote: 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
On Thu, 7 Aug 2008 07:12:01 -0700, Brad
wrote: 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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
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 ..... -- Wag more, bark less "Anti-Spam" wrote: On Thu, 7 Aug 2008 07:12:01 -0700, Brad wrote: 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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
On Thu, 7 Aug 2008 08:55:01 -0700, Brad
wrote: 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 ****s 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. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
On Thu, 7 Aug 2008 08:55:01 -0700, Brad
wrote: 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? |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
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 On Sun, 10 Aug 2008 11:52:17 +0200, Anti-Spam wrote: On Thu, 7 Aug 2008 08:55:01 -0700, Brad wrote: 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? |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
On Sun, 10 Aug 2008 06:35:56 -0700, Gord Dibben <gorddibbATshawDOTca
wrote: 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 On Sun, 10 Aug 2008 11:52:17 +0200, Anti-Spam wrote: On Thu, 7 Aug 2008 08:55:01 -0700, Brad wrote: 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? Sorry, but already tried formatting to "General". |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
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 On Sun, 10 Aug 2008 21:01:26 +0200, Anti-Spam wrote: On Sun, 10 Aug 2008 06:35:56 -0700, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Sun, 10 Aug 2008 11:52:17 +0200, Anti-Spam wrote: On Thu, 7 Aug 2008 08:55:01 -0700, Brad wrote: 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? Sorry, but already tried formatting to "General". |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
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 On Sun, 10 Aug 2008 17:52:36 -0700, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Sun, 10 Aug 2008 21:01:26 +0200, Anti-Spam wrote: On Sun, 10 Aug 2008 06:35:56 -0700, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Sun, 10 Aug 2008 11:52:17 +0200, Anti-Spam wrote: On Thu, 7 Aug 2008 08:55:01 -0700, Brad wrote: 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? Sorry, but already tried formatting to "General". |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
Copy then, in place, EditPaste SpecialValuesOKEsc.
Now delete the source data. Gord On Fri, 29 Aug 2008 12:01:56 +0200, Anti-Spam wrote: 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 On Sun, 10 Aug 2008 17:52:36 -0700, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Sun, 10 Aug 2008 21:01:26 +0200, Anti-Spam wrote: On Sun, 10 Aug 2008 06:35:56 -0700, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Sun, 10 Aug 2008 11:52:17 +0200, Anti-Spam wrote: On Thu, 7 Aug 2008 08:55:01 -0700, Brad wrote: 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? Sorry, but already tried formatting to "General". |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how do you remove leading spaces etc in cells?
Thank you very much, it worked just great.
Mark in Spain. On Fri, 29 Aug 2008 06:11:08 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Copy then, in place, EditPaste SpecialValuesOKEsc. Now delete the source data. Gord On Fri, 29 Aug 2008 12:01:56 +0200, Anti-Spam wrote: 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 On Sun, 10 Aug 2008 17:52:36 -0700, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Sun, 10 Aug 2008 21:01:26 +0200, Anti-Spam wrote: On Sun, 10 Aug 2008 06:35:56 -0700, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Sun, 10 Aug 2008 11:52:17 +0200, Anti-Spam wrote: On Thu, 7 Aug 2008 08:55:01 -0700, Brad wrote: 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? Sorry, but already tried formatting to "General". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove leading spaces | Excel Worksheet Functions | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
How do I add leading spaces to a value? | Excel Worksheet Functions | |||
Remove Leading Spaces | Excel Discussion (Misc queries) | |||
how do I remove spaces from cells that were pasted | Excel Discussion (Misc queries) |