Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    The Finer Points of the MID statement (XL 2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    Happy Holidays to all. <img src=/S/music.gif border=0 alt=music width=97 height=29>

    OK why is this not working:

    <font color=blue>Mid(sTextToClean, lCharacterCounter, 1) =</font color=blue> <font color=red>"" </font color=red>

    is it because the 1 should equal <font color=red>" "</font color=red>, like in a space, but what if I have to replace the character that is in the middle of the string, I will get spaces and that is not what I want? Sorry lCharacterCounter is an For-Next Loop counter that advances by 1 so you could think of it as 1 character at a time.

    What can I use to replace the character with nothing?

    Cordially

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: The Finer Points of the MID statement (XL 2003

    Hi Wassim

    I would go for a straight Replace

    <pre>Sub RemoveMe()

    Dim WSht As Worksheet
    For Each WSht In Worksheets
    WSht.Cells.Replace What:="Jezza", _
    Replacement:="", LookAt:=xlPart, MatchCase:=False
    Next
    End Sub
    </pre>

    Jerry

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Finer Points of the MID statement (XL 2003)

    It is impossible to say why it is not working without knowing a lot more. Like, what is in sTextToClean, and what is the starting value of lCharacterCounter, and what is the last value of lCharacterCounter, and what is the increment value for lCharacterCounter (you did say this was 1). In other words, we would need to see the whole For-Next loop plus any code outside the loop that would affect any of the values mentioned above. It would also help to know what you think the loop is doing and what you want it to do.
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Finer Points of the MID statement (XL 2003

    Now, that would depend on what he is trying to do which is not obvious from the original post!
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: The Finer Points of the MID statement (XL 2003

    Sorry, agreed, I was being a bit assumptive and introducing it as a method not necessarily as a distinct answer <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Jerry

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: The Finer Points of the MID statement (XL 2003

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Jerry and All

    I posted this message and then continued working on the problem and I found a solution that is slow but accurate. I was trying to clean data from a mainframe dump and I needed to cycle through each character of the cell's content and remove unacceptable characters. I used the replace with the Mid statement to replace the characters that I don't want.

    Thanks for the quick reply.

    Cordially

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: The Finer Points of the MID statement (XL 2003)

    You can only use Mid to replace part of a string with another string of exactly the same length. So this will work:
    <code>
    Mid(sTextToClean, lCharacterCounter, 1) = "w"
    </code>
    or
    <code>
    Mid(sTextToClean, lCharacterCounter, 6) = "wassim"
    </code>
    but not
    <code>
    Mid(sTextToClean, lCharacterCounter, 1) = ""
    </code>
    As Jezza recommended, use Replace if you want to replace a substring by another string of a different length, or use concatenation to omit one or more characters:
    <code>
    sTextToClean = Left(sTextToClean, lCharacterCount - 1) & Mid(sTextToClean, lCharacterCount + 1)</code>

Posting Permissions

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