Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
blank spaces in front of a number prevents formatting cell
I was sent an email with a spreadsheet. The columns with numbers cannot be
reformatted or summed up because there is a blank space in front of the number and two blank spaces after the numbers. When I try to reformat the cells to currency, text or anything else it does not work. When I retype the number in the cell , then it works but I have 600 cells with these #'s and I hate to retype. I have tried =trim and =clean but these work for text and not numbers. I have searched and searched but cannot find a way to take out the blank spaces so that these cells can be formatted to currency so I can sum them up. I am just going to retype them but in the future I am curious if anyone else has come up with a solution to this without retyping. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
blank spaces in front of a number prevents formatting cell
Are you sure they are blanks or just non-printable characters? TRIM will work
if they are blanks. Try this for one cell: =VALUE(SUBSTITUTE(A1,CHAR(160),"")) "Teri" wrote: I was sent an email with a spreadsheet. The columns with numbers cannot be reformatted or summed up because there is a blank space in front of the number and two blank spaces after the numbers. When I try to reformat the cells to currency, text or anything else it does not work. When I retype the number in the cell , then it works but I have 600 cells with these #'s and I hate to retype. I have tried =trim and =clean but these work for text and not numbers. I have searched and searched but cannot find a way to take out the blank spaces so that these cells can be formatted to currency so I can sum them up. I am just going to retype them but in the future I am curious if anyone else has come up with a solution to this without retyping. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
blank spaces in front of a number prevents formatting cell
Thank You Toppers,,,
That worked beautifully. Teri "Toppers" wrote: Are you sure they are blanks or just non-printable characters? TRIM will work if they are blanks. Try this for one cell: =VALUE(SUBSTITUTE(A1,CHAR(160),"")) "Teri" wrote: I was sent an email with a spreadsheet. The columns with numbers cannot be reformatted or summed up because there is a blank space in front of the number and two blank spaces after the numbers. When I try to reformat the cells to currency, text or anything else it does not work. When I retype the number in the cell , then it works but I have 600 cells with these #'s and I hate to retype. I have tried =trim and =clean but these work for text and not numbers. I have searched and searched but cannot find a way to take out the blank spaces so that these cells can be formatted to currency so I can sum them up. I am just going to retype them but in the future I am curious if anyone else has come up with a solution to this without retyping. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing 2 extra spaces in front of dates in imported excel doc | Excel Discussion (Misc queries) | |||
CELL IS BLANK BUT NEED IT FILLED WITH SPACES TO A SPECIFIC LENGTH | Excel Worksheet Functions | |||
CELL IS BLANK BUT NEED IT FILLED WITH SPACES TO A SPECIFIC LENGTH | Excel Worksheet Functions | |||
how do i extract a number from a cell with no spaces? | Excel Worksheet Functions | |||
how to add a letter in front of each number in a cell | Excel Discussion (Misc queries) |