Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Numbers vs text (Excel 95)

    I'm dealing with a problem of cells containing numbers apparently "seeing" those numbers as text. Nothing I do can force Excel to see these cells as containing numbers. Nothing except for re-entering the contents of each cell <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> MS has a knowledge base article here, but I wonder if anyone else has run into this problem. I have BTW encountered this in newer versions of Excel.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Numbers vs text (Excel 95)

    Catherine,

    have you tried the following :

    1. In an unused cell enter the number 1.
    2. Copy it.
    3. Select the deviant cells and Paste Special, Multiply.

    Does that convert the "text" to numeric values ?

    Andrew C

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

    Re: Numbers vs text (Excel 95)

    The method Andrew gave you should convert the cells to numbers. However, to avoid problems later, also check the cell format and make sure it is not Text.

    If by chance Andrew's method does not work, can you tell us how the numbers got into the sheet and also maybe upload a sample.
    Legare Coleman

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Numbers vs text (Excel 95)

    The multiply trick won't work for me, because the numbers are codes used in a lookup table. Their position has to to be fixed in place. But basically, any other thing that I do that activates the cell for editing, allows XL to recognize the numeric format.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Numbers vs text (Excel 95)

    Checked the formatting. It is numeric and centered. However, changing it to any other numeric format doesn't help either - until the cell is activated for editing.
    The numbers are - copied and pasted from another spreadsheet. I then have a macro format them. They are used in a lookup formula. Just looking at the formats it appears like both the lookup range and the data have the same formats. However, the lookup formula doesn't perceive them to be the same.
    Now, it has been working for months until my client decided they didn't like the format of the sheet. Even though they "undid" their formatting changes, something has "stuck". Or at least this is as much of the story as I'm going to get.
    Obviously, I can't post the worksheet - due to confidentiality issues. But I have run into this before and normally I just edit or reenter information. But due to the size of this worksheet....
    I think I'm going to write a "repair" macro, probably based on the one shown in the knowledge base article mentioned in my initial post.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Numbers vs text (Excel 95)

    I don't understand what the problem is. The method Andrew recommended does not change the position of anything, it will just change text numbers to numeric values.
    Legare Coleman

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

    Re: Numbers vs text (Excel 95)

    By this description, it sounds like Andrew's method should do exactly what you want.
    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers vs text (Excel 95)

    One last trick that might help:
    - Select the offending range
    - Data, Text to columns, Fixed, Finish
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers vs text (Excel 95)

    >However, the lookup formula doesn't perceive them to be the same

    Is this a problem with the original data or the results of the lookup? If it's the latter, what happens if you change the lookup formula from =vlookup(*,*,*,*,*) to =0+vlookup(*,*,*,*,*)?

    Brooke

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers vs text (Excel 95)

    Also, if the "numbers" are codes to be used in a lookup, it might be better to treat them as text (that is, apply a text format to the cells). Reason is, with a general format or some numeric format, Excel will not take what you type literally. For example, if the code number has a leading zero (like 012345), Excel will drop the leading 0 if the format is a numeric format.

    Why do you want to deal with these codes as numbers? Do you have to perform a numeric calculation with them? If so, try using the VALUE() worksheet function in your subsequent cells.

  11. #11
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers vs text (Excel 95)

    I've run into the same problem on a number of occasions. My solution has been to select the offending column(s) (numbers that are text), copy, Paste Special into Word, Find & Replace all blank spaces with nothing, Copy, and Paste back into Excel. Even if there are no blank spaces in the cells once you Paste Special into Word, this still seems to work. (The copy/paste special into Word does something to the data)

    I used this method recently on a spreadsheet that had over 30,000 records. If there's an easier way of doing this, I'm certainly open to suggestions.
    Thanks,
    Caroline in lala-land

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

    Re: Numbers vs text (Excel 95)

    Andrew's suggestion in the second post in this thread will do the same thing much easier.
    Legare Coleman

  13. #13
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers vs text (Excel 95)

    You're right, Andrew's method works like a charm. I should have tried it first before posting my solution. Thanks!
    Thanks,
    Caroline in lala-land

  14. #14
    Matt Heikkila
    Guest

    Re: Numbers vs text (Excel 95)

    I had a similar problem, in Excel97. Change a cell to text format (with a number in it) and any formulas using that cell still use the value, until you edit the cell. Similarly, change a cell to a numeric format that was text formatted, and the value is NOT used until the cell is edited. I wanted to toggle between text and numeric to prevent certain data values from being used in averages, STDEV's etc. while still preserving the data. I wrote a macro that takes an entire row and converts it to text, and another to retrieve the values from text formated cells. Here is the retrieval code:

    Sub ConvertRowTextToNumbers()
    '
    ' Macro to convert every text entry in an entire row into
    ' values, dates, etc.
    ' for Excel97
    '----------
    Dim x As Variant
    Dim strx As String
    colnum = ActiveCell.Column
    ActiveCell.Offset(0, 1 - ActiveCell.Column).Activate 'go to first cell in row
    On Error GoTo CnvRTTN_Err_Handler 'to stop at end of row
    Do 'until error occurs
    If Not IsEmpty(ActiveCell.Value) Then 'skip blank cells
    If TypeName(ActiveCell.Value) = "String" Then 'only act on string values
    strx = Trim(ActiveCell.Text) 'grab text value for dates, times, and formulas
    If Left(strx, 1) = "=" Then
    x = strx
    ActiveCell.NumberFormat = "General" 'general format
    ActiveCell.Formula = x 'enter previous value as formula
    ElseIf IsNumeric(strx) Then 'do this before date check!!!!
    x = CDbl(strx) 'convert all numbers to largest type option
    ActiveCell.NumberFormat = "General" 'general format
    ActiveCell.FormulaR1C1 = x 'enter previous value as double
    ElseIf IsDate(strx) Then
    x = CDate(strx) 'this keeps 12:34:56 from becoming 0.524259259259259
    ActiveCell.NumberFormat = "General" 'general format so bext date format is used
    ActiveCell.FormulaR1C1 = x 'enter previous value as date
    Else
    'leave text value as is
    End If
    End If
    End If
    ActiveCell.Range("b1").Activate 'next column (end of row forces error)
    Loop
    RowComplete:
    ActiveCell.Offset(1, colnum - ActiveCell.Column).Activate 'go to cell in next row at same column where started
    On Error GoTo 0 'reset error handler
    '----------------

    Exit Sub
    CnvRTTN_Err_Handler:
    Dim ErrNum As Long
    ErrNum = Err.Number
    Select Case ErrNum
    Case 1004 'end of row found
    Resume RowComplete
    Case Else
    Err.Raise ErrNum ' show the unexpected error
    End Select

    End Sub

    HTH

  15. #15
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Numbers vs text (Excel 95)

    Sorry, Legare and Andrew <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> what can I say - I was brain dead when I answered earlier (fortunately I've recovered). <big>Of course</big> the paste special, multiply will work.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Page 1 of 3 123 LastLast

Posting Permissions

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