String Manipulation
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: String Manipulation

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    All,

    I have the following situation.

    I have ranges named the following:

    2002.03.01!Monday2
    2002.03.01!Thursday
    2002.03.01!Friday
    2002.03.01!SaturdayWO2
    etc.

    Is there anyway I can capture all information to the right of the ! and set it equal to a variable? I have been trying Left and Right with no success.
    Thanks in advance,
    Patrick

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If your data is in cell A1,

    =MID(A1,FIND("!",A1)+1,1000)

    this will isolate everything after the !.
    You can then refer to this result in other formulas.
    (If the data after the ! is likely to be longer than 1000 characters, increase the number)

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks! I was actually trying just that!


    Thanks in advance,
    Patrick

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 06:37, poleary2000 wrote:
    Thanks! I was actually trying just that!


    How about using data>text to columns, select delimited, click next and as delimiter choose
    other and put an Exclamation mark in the other box, click next, click in the left column, select "do not import column (skip)", click Finish..

    No need for formulas

    Regards,

    Peo Sjoblom

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually, I need to do this with VBA. Neither method works. Is there a VBA function that can help?
    Thanks in advance,
    Patrick

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Also, what happens if the ! is not found? Can I make it go to another part of the program?
    Thanks in advance,
    Patrick

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
  •  

 

 
DMCA.com