Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help removing "space" character (question updated)
I am copying data from the web that's in a spreadsheet format and which can
be dropped straight into Excel. The information is yearly values that are displayed for each month of the year. I have two problems: 1. The data is "backward" - on the sheet I want to input this data, it starts in year 2004 and moves month by month down the spreadsheet, ending with 2008. The data on the website *begins* 12/08 and moves down the page so that the earliest data is at the bottom rather than the top. (I resolved this by putting the data in the order I want it and doing a vlookup against the data I'm pasting from the web). 2. Second issue is that there is an extra space in the monthly information at the end of the data. Conseqeuently, when I paste the vlookup result info (I also copy that info and paste again in the sheet as a value . . .) each cell that has data has a space at the end of it. I can go into each one and remove the space but the "find and replace" feature won't remove the space universally. I have about 150 companies for which I have to update 2006, 2007 and 2008 data by month so being able to copy from the site, do a tiny bit of manipulation and pasting the result into my ultimate spreadhseet would be great and a real time saver (and much faster than hand-keying all the info!). Thanks for any ideas you have on making this process faster and more efficient. mm |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help removing "space" character (question updated)
2. presuming yr data starts in A1 (in some other column) use the
formula: =LEFT(A1,LEN(A1)-1) to get rid of spaces in the end, then copy and paste special as values HIH |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help removing "space" character (question updated)
You can also use the following formula to remove the trailing space:
=TRIM(A1) Copy down the column as far as necessary, then copy the entire column with the formula and do a EDIT/PASTE SPECIAL/VALUES over the original data. You can then remove the helper column w/the TRIM function. -- Kevin Backmann "Jarek Kujawa" wrote: 2. presuming yr data starts in A1 (in some other column) use the formula: =LEFT(A1,LEN(A1)-1) to get rid of spaces in the end, then copy and paste special as values HIH |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help removing "space" character (question updated)
Hi
Some of the spaces may be the non-break space character (0160). Assuming your data is in column B, in a spare column on the sheet enter =SUBSTITUTE(SUBSTITUTE(B1,CHAR(160),"")," ","") and copy down as far as required. This will remove both type of space. Then, copy this new column of dataPaste SpecialValues over the data you have in column B. The extra column can then be deleted. To reverse your data, just do the following. Assuming you have data in columns A:D that you want to reverse. In cell F1 enter =INDEX(A:A,COUNTA(A:A)+1-ROW()) Copy across through G1:I1, then copy down as far as you require -- Regards Roger Govier "smartgal" wrote in message ... I am copying data from the web that's in a spreadsheet format and which can be dropped straight into Excel. The information is yearly values that are displayed for each month of the year. I have two problems: 1. The data is "backward" - on the sheet I want to input this data, it starts in year 2004 and moves month by month down the spreadsheet, ending with 2008. The data on the website *begins* 12/08 and moves down the page so that the earliest data is at the bottom rather than the top. (I resolved this by putting the data in the order I want it and doing a vlookup against the data I'm pasting from the web). 2. Second issue is that there is an extra space in the monthly information at the end of the data. Conseqeuently, when I paste the vlookup result info (I also copy that info and paste again in the sheet as a value . . .) each cell that has data has a space at the end of it. I can go into each one and remove the space but the "find and replace" feature won't remove the space universally. I have about 150 companies for which I have to update 2006, 2007 and 2008 data by month so being able to copy from the site, do a tiny bit of manipulation and pasting the result into my ultimate spreadhseet would be great and a real time saver (and much faster than hand-keying all the info!). Thanks for any ideas you have on making this process faster and more efficient. mm |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help removing "space" character (question updated)
Works! Thanks much!
"Kevin B" wrote: You can also use the following formula to remove the trailing space: =TRIM(A1) Copy down the column as far as necessary, then copy the entire column with the formula and do a EDIT/PASTE SPECIAL/VALUES over the original data. You can then remove the helper column w/the TRIM function. -- Kevin Backmann "Jarek Kujawa" wrote: 2. presuming yr data starts in A1 (in some other column) use the formula: =LEFT(A1,LEN(A1)-1) to get rid of spaces in the end, then copy and paste special as values HIH |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help removing "space" character (question updated)
Thanks for the global solution in your prior post. I'm gonna' file that one
away for future reference. -- Kevin Backmann "Roger Govier" wrote: Hi Some of the spaces may be the non-break space character (0160). Assuming your data is in column B, in a spare column on the sheet enter =SUBSTITUTE(SUBSTITUTE(B1,CHAR(160),"")," ","") and copy down as far as required. This will remove both type of space. Then, copy this new column of dataPaste SpecialValues over the data you have in column B. The extra column can then be deleted. To reverse your data, just do the following. Assuming you have data in columns A:D that you want to reverse. In cell F1 enter =INDEX(A:A,COUNTA(A:A)+1-ROW()) Copy across through G1:I1, then copy down as far as you require -- Regards Roger Govier "smartgal" wrote in message ... I am copying data from the web that's in a spreadsheet format and which can be dropped straight into Excel. The information is yearly values that are displayed for each month of the year. I have two problems: 1. The data is "backward" - on the sheet I want to input this data, it starts in year 2004 and moves month by month down the spreadsheet, ending with 2008. The data on the website *begins* 12/08 and moves down the page so that the earliest data is at the bottom rather than the top. (I resolved this by putting the data in the order I want it and doing a vlookup against the data I'm pasting from the web). 2. Second issue is that there is an extra space in the monthly information at the end of the data. Conseqeuently, when I paste the vlookup result info (I also copy that info and paste again in the sheet as a value . . .) each cell that has data has a space at the end of it. I can go into each one and remove the space but the "find and replace" feature won't remove the space universally. I have about 150 companies for which I have to update 2006, 2007 and 2008 data by month so being able to copy from the site, do a tiny bit of manipulation and pasting the result into my ultimate spreadhseet would be great and a real time saver (and much faster than hand-keying all the info!). Thanks for any ideas you have on making this process faster and more efficient. mm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help removing "space" character from imported data | Excel Discussion (Misc queries) | |||
Can I automatically add space after ea. row ("Space After" in Word | Excel Discussion (Misc queries) | |||
how do I type "itis" without Excel putting a space "it is"? | Excel Worksheet Functions | |||
"Disk is Full" add-on question to "Can't reset last cell" post tod | Excel Discussion (Misc queries) | |||
NEED HELP-----Removing a space at the end of a string of character | Excel Discussion (Misc queries) |