Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    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.

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Number Formatting (2002)

    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
    <code>=VALUE(LEFT(A1,LEN(A1)-1))</code>

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

  3. #3
    3 Star Lounger
    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 non-breaking space at the end?
    2. A litttle elaboration on the formula?

    Again thanks.

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Number Formatting (2002)

    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.

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

  6. #6
    Uranium Lounger
    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 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>
    =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 non-breaking space.
    Legare Coleman

  7. #7
    3 Star Lounger
    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 non-breaking space? Also, why doesn't a EDIT, Find, "space", Replace get rid of this type of space?

    Thanks.

  8. #8
    Platinum Lounger
    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 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)

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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 "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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •