Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Text cell and number cell

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    220
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,
    I have just come across a problem. I extracted from a database fields which have numbers in it but when they are received in Excel they are actually text. Now I am faced with the problem of converting them to a number field. I wanted to use the vlookup function and wanted to compare two numbers but because one is a text field and the other a number it returns #N/A.

    Thanks for your help
    Andonny

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Try,

    Code:
    Sub tester()
    Dim Cell As Range, Rng1 As Range
    
    Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A"))
    
    For Each Cell In Rng1
    If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then
        Cell = CDbl(Cell)
    End If
    Next Cell
    End Sub
    Note that this code also converts mainframe downloads thathave trailing negative signs (4- for example).

    Adjust your range to suit.

    Bye,
    Jay

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Select your values, choose the Data | Text to Columns... menu command and press [ Finish ].

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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