Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 77
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 77
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing 2 extra spaces in front of dates in imported excel doc Hannah Excel Discussion (Misc queries) 4 April 12th 06 07:37 PM
CELL IS BLANK BUT NEED IT FILLED WITH SPACES TO A SPECIFIC LENGTH CHARI Excel Worksheet Functions 4 August 30th 05 12:54 AM
CELL IS BLANK BUT NEED IT FILLED WITH SPACES TO A SPECIFIC LENGTH מיכאל (מיקי) אבידן Excel Worksheet Functions 0 August 29th 05 09:55 PM
how do i extract a number from a cell with no spaces? Mulvaney Excel Worksheet Functions 4 July 26th 05 09:15 PM
how to add a letter in front of each number in a cell nmodafferi Excel Discussion (Misc queries) 15 June 16th 05 08:58 PM


All times are GMT +1. The time now is 07:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"