Number Formatting (2002)

Topic

New Reply

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.

Reply | Quote

Replies

The cells contain a non-breaking space at the end. A couple of methods to convert them to numbers are:

1/ Use the following formula
=VALUE(LEFT(A1,LEN(A1)-1))

2/ Use a macro, see TrimALL macro by David McRitchie.

Reply | Quote

Thanks a bunch.

1. How did you determine there was a non-breaking space at the end?
2. A litttle elaboration on the formula?

Again thanks.

Reply | Quote

1/ I used a free add-in by Chip Pearson, Cell View. This shows you what every character is, in this case it ended with character code 160 which is a non-breaking 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 non-breaking space.
The VALUE function then converts the value to a number.

Reply | Quote

Ok… Downloaded… How did you know that character code 160 is a non-breaking space? Also, why doesn’t a EDIT, Find, “space”, Replace get rid of this type of space?

Thanks.

Reply | Quote

There are tables such as this one that you can use to find out what the codes are.

A computer recognises the space and non-breaking space as different characters, so Edit – Find – Space will not find the non-breaking space. When you search for a space it is searching for the character with ASCII decimal code 32, so it does not find the non-breaking space (ASCII decimal code 160)

Reply | Quote

It is called a “non-breaking 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

Reply | Quote

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 non-breaking space from all selected cells.

Reply | Quote

Another way to find out that there is a non-breaking 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 non-displaying character at the end of the string.

2- Once you have determined that there is a non-displaying character at the end of the string, put the formula below in an empty cell in row 1 (say B1)


=CODE(RIGHT(A1,1))


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 non-breaking space.

Reply | Quote