Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Test For Apostrophe (XP)

    This is a two part question-

    Part 1
    I would like to do a test on various cells (over 30K) whose contents is either a value or text which is prefixed with an apostrophe. Example: '5002 or '5834N. There are some cells whose contents are not prefixed with the apostrophe which I am trying to identify. I thought about using Chr() and Cells("Type", XX) to determine if the cell is a value or text but have not had any luck.

    Part 2
    I am using advanced filtering on the same column whose cell contents are the same mix as mentioned above. The column contains a 1000 and 5000 series of numbers or text such as '1000, '1010, 1020, '5002, '5834N, 5900 and etc.. My issue is with the criteria as I can filter out all the text for 1* and 5* but am unable to filter out the values using the wildcard.

    Thanks,
    John

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Test For Apostrophe (XP)

    If I understand your questions:

    1) you can create a custom function to determine whether it has a prefixCharacter. Add this to a module:

    <pre>Function HasPrefix(rng As Range) As Boolean
    HasPrefix = (rng.Cells(1).PrefixCharacter = "'")
    End Function</pre>


    Call it in a cell using something like:
    <pre>=HasPrefix(A1)</pre>


    2) You could use a criteria like this (the criterion Header must be blank with a formula as criteria):
    <pre>=OR(LEFT(A2,1)="1",LEFT(A2,1)="5")</pre>


    This will find any text starting with "1" or "5" or any number that starts with 1 or 5 (eg 1,5, 10-19, 50-59,100-199, 500-599, 1000-1999, 5000-5999, etc)


    Steve

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Test For Apostrophe (XP)

    What do you want to do with the results? In this sample, Excel treats all but cell A1 as containing text:
    <table border=1><td></td><td align=center>A</td><td align=center>1</td><td align=right>5400</td><td align=center>2</td><td align=right>'5400</td><td align=center>3</td><td>'5400N</td><td align=center>4</td><td>5400N</td></table>
    You can convert them all to text using Cells | Format | Text and your Filters will return sorts consistent with Excels treatment of text. Otherwise can you tell us more of what you want to accomplish.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Test For Apostrophe (XP)

    After thinking about it, my function would be better as

    <pre>Function HasPrefix(rng As Range) As Boolean
    HasPrefix = (rng.Cells(1).PrefixCharacter <> "")
    End Function</pre>


    This would give TRUE if it has any prefix. My original would only give TRUE if the prefix was a single quote(apostrophe,[']), but would have given FALSE if the prefix were a dbl-quote["] or a caret (^) which are possible if the "transition navigation" property is enabled.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test For Apostrophe (XP)

    John,

    I never thought about using the text formating. After testing, this approach does not work as the value never is filtered out along with the text.

    What I'm after is to filter cells either text or value that begin with a 5 or a 1.

    John

Posting Permissions

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