Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a list of several thousand rows that contain invoice numbers and need to validate if they are all in either a numeric or alpha numeric format. Is it possible to to make this determination. The string of text can be varying lengths and there are no constants for this data so I'm thinking I would somehow need to look at every character in the string to make this determination. Any thoughts or help on this would be appreciated.

    Thanks in advance!

  2. #2
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='b0bito' post='768703' date='02-Apr-2009 01:02']I have a list of several thousand rows that contain invoice numbers and need to validate if they are all in either a numeric or alpha numeric format. Is it possible to to make this determination. The string of text can be varying lengths and there are no constants for this data so I'm thinking I would somehow need to look at every character in the string to make this determination. Any thoughts or help on this would be appreciated.

    Thanks in advance![/quote]

    if you want to find out if the cell contain text or numbers
    try this =ISTEXT(A1), return True if the cell contain text

    HTH

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  3. #3
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='b0bito' post='768703' date='01-Apr-2009 12:02']I have a list of several thousand rows that contain invoice numbers and need to validate if they are all in either a numeric or alpha numeric format. Is it possible to to make this determination. The string of text can be varying lengths and there are no constants for this data so I'm thinking I would somehow need to look at every character in the string to make this determination. Any thoughts or help on this would be appreciated.

    Thanks in advance![/quote]


    Thanks for the reply. The ISTEXT option does seem to work if I was enter it in the sheet and drag the formula down. Since I will not actually be entering any formulas in the sheet. Is there a way to include this through Vb. I originally intended to use a for next loop to look at every cell in column A to determine if it is text or alphanumeric. Is there a way to perform this type of check with out adding any formulas to my sheet?

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What do you want the end result to be?

  5. #5
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='768714' date='01-Apr-2009 12:28']What do you want the end result to be?[/quote]


    I'm just looking to perform a count of numeic and alphanumeric entries in cells entered into column A.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In VBA, the number of numeric values (including date values) is

    Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers).Count

    and the number of text values (including numbers stored as text) is

    Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues).Count

    You can prefix Range("A:A") with a reference to a worksheet if necessary:

    Worksheets("MyData").Range("A:A").SpecialCells(xlC ellTypeConstants, xlNumbers).Count

  7. #7
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='b0bito' post='768730' date='01-Apr-2009 13:06']I'm just looking to perform a count of numeic and alphanumeric entries in cells entered into column A.[/quote]


    Excellent. Thanks for the suggestions. Very much appreciated.

Posting Permissions

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