Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: test first character in each cell to see if alpha

  1. #1
    Join Date
    14th September 2004
    Posts
    12

    test first character in each cell to see if alpha

    I want to be able to go through a range (all in col A) and if the first non-blank character is a Letter, change the font and cell color.

    In excel I can use "Trim" to clear leading blanks, then use "code" to test the ascii value of the first character.

    in VBA I can use the trim and assign cell valuable to a variable, but don't know how to pick off first character in string and test to see if it is an alpha (versus a blank, or a number)

    thanks

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,539

    Re: test first character in each cell to see if alpha

    The VBA equivalent of CODE is Asc.

    And you can use the Left function like you can on a worksheet.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: test first character in each cell to see if alpha

    Hi wbsmith,

    THis code will change the cell color to res if the cell is not empty and the first character of the cell value is not numeric.

    VB:
    Sub FindFirstAlpa() 
         
        Dim c As Range 
         
        For Each c In Range("A1", Range("A65536").End(xlUp)) 
            If Not IsNumeric(Left(c, 1)) And Not c = Empty Then 
                c.Interior.ColorIndex = 3 
            End If 
        Next c 
    
    
    hope this helps

    Bill


    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    30th March 2004
    Location
    Oregon, US
    Posts
    166

    Re: test first character in each cell to see if alpha

    You can also do this with native conditional formatting...

    1) Select the entire column A (click the A column header)
    2) Format --> Conditional Formatting --> Formula Is ..
    3) ..
    VB:
    =(Not(ISBLANK(A1)))*((CODE(UPPER(LEFT(TRIM(A1),1)))>64)*(CODE(UPPER(LEFT(TRIM(A1),1)))<91)) 
    
    
    4)Format as desired.


    HTH

  5. #5
    Join Date
    14th September 2004
    Posts
    12

    Re: test first character in each cell to see if alpha

    all, great info, thanks. quick general questions
    1) I've read much of vba book by walkenback. Great stuff, but jumps around. Is there a programming reference that presents vba programming in a more comprehensive fashion? There are so many functions, objects, ... that I don't feel comfortable I know how they really function

    2) Mental glitch: what does the "Set" command do and when do you use it instead of an assignment "=" operator

    3) In conditional formatting using formula, how does it know what row to evaluate...that is, to test A1 for row 1, A2 for row 2? what if you wanted A1 to test A2?

    Help here has been invaluable. I was CS guy in college, but years ago now. Lots of fun to be able to play, but only because of the excellent help I can get when I am stuck. tried the cond formatting...am going to try the macro route too.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Return Number Before Alpha Character
    By thelarster in forum EXCEL HELP
    Replies: 3
    Last Post: February 12th, 2008, 07:44
  2. Extract String After First Alpha Character
    By EROEI in forum EXCEL HELP
    Replies: 3
    Last Post: July 27th, 2007, 05:11
  3. Logical Test for a special character
    By ecf1956 in forum EXCEL HELP
    Replies: 2
    Last Post: April 15th, 2005, 17:02
  4. Alpha Character to return 2 digit number.
    By Tbone in forum EXCEL HELP
    Replies: 7
    Last Post: August 14th, 2004, 22:12

Bookmarks

Posting Permissions

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