Results 1 to 9 of 9
Thread: Number Formatting (2002)

20051124, 17:15 #1
 Join Date
 Mar 2004
 Location
 North Carolina, USA
 Posts
 268
 Thanks
 0
 Thanked 0 Times in 0 Posts
Number Formatting (2002)
I copied and pasted numerical data into Excel from the internet. I have tried to clear formatting, entered the number 1 and selected the numbers and selcted paste special, values, multiply, etc to try and convert these numbers into a format I can use.
I have attached the numbers. Any ideas?
Thanks.

20051124, 17:50 #2
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Number Formatting (2002)
The cells contain a nonbreaking space at the end. A couple of methods to convert them to numbers are:
1/ Use the following formula
<code>=VALUE(LEFT(A1,LEN(A1)1))</code>
2/ Use a macro, see TrimALL macro by David McRitchie.

20051125, 14:09 #3
 Join Date
 Mar 2004
 Location
 North Carolina, USA
 Posts
 268
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Number Formatting (2002)
Thanks a bunch.
1. How did you determine there was a nonbreaking space at the end?
2. A litttle elaboration on the formula?
Again thanks.

20051125, 14:24 #4
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Number Formatting (2002)
1/ I used a free addin by Chip Pearson, Cell View. This shows you what every character is, in this case it ended with character code 160 which is a nonbreaking space.
2/ The formula
LEN(A1) is used to calculate how many characters are in the cell
LEFT(A1,x) will take the first x characters from A1, in this case the x = LEN(A1)1 which is the number of characters in the cell minus 1, so this removes the final nonbreaking space.
The VALUE function then converts the value to a number.

20051125, 14:46 #5
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Number Formatting (2002)
If you want a third method of doing this you can use Find and Replace.
With the cursor in the "Find what:" box, hold down the Alt key and type 0160 (using the number keypad). You then leave the "Replace with:" box empty. Clicking "Replace All" will then remove the nonbreaking space from all selected cells.

20051125, 17:08 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Number Formatting (2002)
Another way to find out that there is a nonbreaking space at the end without using the addin would be to:
1 Select one of the cells (say A1), and then click in the formula bar so that the cursor shows there in edit mode. Then use the left and right arrows to move the cursor around. This should show that there is a nondisplaying character at the end of the string.
2 Once you have determined that there is a nondisplaying character at the end of the string, put the formula below in an empty cell in row 1 (say B1)
<code>
=CODE(RIGHT(A1,1))
</code>
Fill the formula down as far as the data goes. That should display 160 in all of those cells. 160 is the code for the nonbreaking space.Legare Coleman

20051125, 17:24 #7
 Join Date
 Mar 2004
 Location
 North Carolina, USA
 Posts
 268
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Number Formatting (2002)
Ok... Downloaded... How did you know that character code 160 is a nonbreaking space? Also, why doesn't a EDIT, Find, "space", Replace get rid of this type of space?
Thanks.

20051125, 17:44 #8
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Number Formatting (2002)
There are tables such as this one that you can use to find out what the codes are.
A computer recognises the space and nonbreaking space as different characters, so Edit  Find  Space will not find the nonbreaking space. When you search for a space it is searching for the character with ASCII decimal code 32, so it does not find the nonbreaking space (ASCII decimal code 160)

20051125, 18:17 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Number Formatting (2002)
It is called a "nonbreaking space" since it is printed like a space but is not treated like a space (hence the problem you encounter). Excel knows to ignore spaces and convert to numbers. Since your item has a text with the numbers, it must be text and not a number...
Steve