Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: ' before numbers and text

    I have made some subtotals. Now before some of the (not all) totals there is an ' .For example
    1 totals
    2 totals
    '3 totals
    '4 totals
    5 totals
    etc.

    Now it is not possible to use the VLOOKUP function. I want to search on 1,2,3,4,5 (I removed the "totals").

    Any solutions????

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    What's interesting is that if you select one of the cells with the ' in it, but don't edit it, once you move out of the cell, then the number appears appropriately.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Posts
    6
    If I click the cell (cursor appears after the text) and the press enter. the ' still is before the text. I really have the remove the ' manually.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    If you select the cell and then hit F2, it appears to be the same.

    Hoever, if you want to remove that you can use something like in C2:

    =LEFT(B2,LEN(B2)-7)

    Then copy down the column. (The "7" stands for the space plus Totals = " Totals")

    Then format the cells as general. It may be at that point that selecting the cell and hitting F2 will change it to the number.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65
    Here's some VBA code I use to remove tick marks.

    sub TickRemover()

    Range("A1").select

    Do
    ActiveCell = ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    Loop Until ActiveCell = ""

    End Sub

    Enjoy!
    -Mike

  6. #6
    Join Date
    Mar 2004
    Posts
    6
    Thanx This works great!!!

  7. #7
    Join Date
    Mar 2004
    Location
    Glasgow
    Posts
    8

    Remove the ticks

    To remove the ', couldn't you just have done a copy/Paste Special and multiplied all the cells in the range by 1 to force them to numbers? That's how I do it anyway.


    Andrew H

Posting Permissions

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