+ Reply to Thread
Results 1 to 6 of 6

Decoding a string

  1. #1
    Forum Contributor
    Join Date
    09-02-2005
    Posts
    146

    Decoding a string

    I've got a string I'd like to decode. Based on the contents of that string, I'd like to manipulate the 3 cells directly to the right of the string's cell.

    The string will always be formatted like this:
    (integer, integer, integer) with three singles next to it.

    String Examples: (3, 0, 1)
    (0, 0, 5)
    (-2, 4, 0)
    (-1, 1, -1)

    What I would like to do is detect if the string contains a 0, if it does, find out if the 0 is in the first, second, or third position (it can be in up to 2 of them), then based on the zero positions, turn the corresponding number in the cell to the right into a "don't care" cell.

    In other words:
    (3, 0, 1) 75.6 833 43.9
    becomes
    (3, 0, 1) 75.6 xxx 43.9


    I know it is possible to find if there is a zero present by using the following:
    =IF(ISERROR(FIND("0",A3)),0,1)

    Also it would be possible to use a bunch of =left and =right to try and trim things out to find out where the 0's are, but I was hoping there is a simpler method I'm not considering. Perhaps there is a function like "CharAt" available in VBA?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Decoding a string

    Hello wilro85,

    If you have a lot of data, say 1000 plus lines, you would be better off using VBA than formulae to this. Unless of course, you don't mind the wait. There are certain cases where VBA is far more expedient in processing data than native formula. If you don't have a lot of data stick with a formula based solution.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Decoding a string

    not only that, where are the numbers in the 3 cells to the right coming from ?
    they cant be in there with a formula or are they the result of a formula?
    but a few left/right/ mids would do it easily enough and are "simple"
    =IF(LEFT(TRIM(A1),2)="(0","xxx",E1)
    =IF(MID(A1,FIND(",",A1)+2,1)="0","xxx",F1) (note the+2 depends on whether cell has spaces after , or not)
    =IF(RIGHT(TRIM(A1),2)="0)","xxx",G1)
    Last edited by martindwilson; 08-03-2009 at 06:11 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Contributor
    Join Date
    09-02-2005
    Posts
    146

    Re: Decoding a string

    Well, I opted to do the don't care before the string was created. It adds another 3 if statements that I have to search through, but ultimately it probably won't slow the program that badly.

    I'm still curious what programmatic methods there are for finding specific characters in a string. In C you can march through a for loop going from 1 to stringlength and use the CharAt command to know what everything is. Are there similar options in VBA?

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Decoding a string

    Hello wilro85,

    The VBA string command Mid will let you specify the starting point, and number of characters to return from the string.
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    09-02-2005
    Posts
    146

    Re: Decoding a string

    Yeah, that is what I was looking for. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1