Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Force it to be text (2000)

    I have broken an address list into several columns - Example address: 405 8th Ave. #5 - Column A is the number (405), Column B is the street (8th Ave), column C (#5) is anything else, such as an apartment number. My problem is those addresses with 1/2 in the number (405 1/2 8th Ave). I CANNOT get Excel to accept 405 1/2 as text. At some point in manipulation, it "believes" it is a number. I've put spaces between the 405 and the 1/2, I've inserted a space before 405, I've formatted as text, I've preceded with an apostrophe - and every time, Excel will allow me to use that in a formula, treating it as if it were 405.5 and often actually changing it to 405.5. Because I'm eventually going to take this to Access, I need the column to be treated as text.

    Frustration reigns! <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Force it to be text (2000)

    What do you mean by "At some point in manipulation"? If you format column A as text, 405 1/2 will be treated as text. But if you try something like =A1+3 or =A1*2, then you will force Excel to try and see if cell A1 can be interpreted as a number.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Force it to be text (2000)

    I agree with Hans that you must be turning the 'text numbers' into numbers at some point for this to happen. One trick to consider is to Find-&-Replace "1/2" with the ASCII 171 symbol "
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Force it to be text (2000)

    Thank you both! I was indeed using the cell in a formula - to test whether it was text or a number. Try this: type 405 1/2 into a cell - format as text. It changes to 405.5 (Yes, if you START out with the cell pre-formatted, you can avoid this). Do a search and replace of .5 with [space] 1/2 - it does, but even though it is "text" the formula bar at the top of the screen reports 405.5!
    I've gotten it into Access cleanly, with a bit of skullduggery- but I'm still a bit miffed at Excel for "helping" me in ways I don't want to be helped.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Force it to be text (2000)

    It's true that given half a chance, Excel will interpret the data entered by the user as numeric. In most circumstances, that is a desirable feature - it allows users to enter a number, a date or a time in many ways. If you want to avoid it, tell Excel beforehand that you're going to enter text by setting the number format for a column to text. You might prefer otherwise, but then lots of other users would complain...

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force it to be text (2000)

    You must format the cell as Text BEFORE you enter anything into it that might be interpreted as a number. Changing the format to Text after Excel thinks there is a number in the cell will not convert the number into text. There are two ways to accomplish what you want:

    1- As stated above, format the cells as Text before entering the data into the cell.

    2- When you enter the 405 1/2 into the cell, put a single quote as the first character like this: '405 1/2 The quote will not display or print, but will force Excel to make the value a Text entry.
    Legare Coleman

Posting Permissions

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