Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removal of 100's of spacing in numbers
I want to be able to use the replace command, but it wont find the spaces
for me. When I copy tables with numbers from various sources, I get spaces inside and after the numbers. E.g. 1 234 I know the use of SUBSTITUTE and other functions, but it is not an effective way of working when there are so many columns. I have tried to change the cell format to text, but still no luck in finding the spaces with the replace command. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removal of 100's of spacing in numbers
Might be some other character.
Place your cursor between 1 and 2 and copy Select the columns with numbers Press Ctrl+H In the Find box press Ctrl+V for the space like character Leave the Replace With box blank and press Replace All Best of luck. -- Thanx & Best Regards, Faraz! "cas" wrote: I want to be able to use the replace command, but it wont find the spaces for me. When I copy tables with numbers from various sources, I get spaces inside and after the numbers. E.g. 1 234 I know the use of SUBSTITUTE and other functions, but it is not an effective way of working when there are so many columns. I have tried to change the cell format to text, but still no luck in finding the spaces with the replace command. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removal of 100's of spacing in numbers
It may be some other non-printable character, such as CHAR(160)
You can either try copying the space from your data and then do a find&replace on that, or you could try and determine what exact character it is via: =CODE(MID(A2,2,1)) If it's a true space, it should be code 32. Check out the XL help topic on CLEAN for more info on non-printable characters. -- Best Regards, Luke M "cas" wrote in message ... I want to be able to use the replace command, but it won't find the spaces for me. When I copy tables with numbers from various sources, I get spaces inside and after the numbers. E.g. "1 234 " I know the use of SUBSTITUTE and other functions, but it is not an effective way of working when there are so many columns. I have tried to change the cell format to text, but still no luck in finding the spaces with the replace command. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removal of 100's of spacing in numbers
try
Sub replacespaces() Cells.Replace " ", "" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "cas" wrote in message ... I want to be able to use the replace command, but it wont find the spaces for me. When I copy tables with numbers from various sources, I get spaces inside and after the numbers. E.g. 1 234 I know the use of SUBSTITUTE and other functions, but it is not an effective way of working when there are so many columns. I have tried to change the cell format to text, but still no luck in finding the spaces with the replace command. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removal of 100's of spacing in numbers
You are totally right. I copied it to Word where I could see that it was
"hard spacing" (don't know the correct word for it in English). A bit ashamed that I didnt try to copy the character to the replace-box. Maybe a too easy solution :) "Faraz Ahmed Qureshi" wrote: Might be some other character. Place your cursor between 1 and 2 and copy Select the columns with numbers Press Ctrl+H In the Find box press Ctrl+V for the space like character Leave the Replace With box blank and press Replace All Best of luck. -- Thanx & Best Regards, Faraz! "cas" wrote: I want to be able to use the replace command, but it wont find the spaces for me. When I copy tables with numbers from various sources, I get spaces inside and after the numbers. E.g. 1 234 I know the use of SUBSTITUTE and other functions, but it is not an effective way of working when there are so many columns. I have tried to change the cell format to text, but still no luck in finding the spaces with the replace command. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Revome blank spacing from ending Numbers | Excel Worksheet Functions | |||
Removal of text from a cell containing both text and numbers | Excel Discussion (Misc queries) | |||
deleting 100's of pictures, keep data | Excel Worksheet Functions | |||
Still no joy - trying to view 100's of file TITLES | Excel Discussion (Misc queries) | |||
Formatting 100's and 1000's | Excel Discussion (Misc queries) |