Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    551
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching for and deleting letter (Excel xp)

    In b1.xls attached, i am trying to search B7:B14 and if there is a hyphen I want to look in the A column at the letter within the parens. Whatever letter is there i need to delete it from the letters in Col C7:C14.

    So in this case I need to delete the "H" from all the cells C7:C14. Also all the letter "L"s.

    I started to write the code but this is as far as i got...

    Sub LookForHyphen()
    Dim rCell As Range
    Dim rLetter As String

    Sheets("S6").Select
    Range("B7:B14").Select
    For Each rCell In Selection
    If Right(rCell.Value, 1) = "-" Then
    rLetter = Mid(rCell.Offset(0, -1).Value, 1, 1) 'find the letter
    'delete the letter and comma from C7:C14

    End If
    Next




    End Sub

    'look for a hyphen
    'if there is a hyphen, go to A column and find letter
    'delete that letter from column C

    Thank you for the help.
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Searching for and deleting letter (Excel xp)

    The value of cells B10 and B13 is not a hyphen, but a hyphen followed by a space, so you can't just test the last character.

    The text between the parentheses in column A can be more than one letter, so you have to be very careful in getting it from column A and deleting it from column C.

    Try this code; I have provided lots of comments in between the code lines. Since I tested in Excel 97, I couldn't use the Replace function; I have indicated where you could use this in XP.

    Sub LookForHyphen()
    Dim ws As Worksheet
    Dim rColumnB As Range
    Dim rCellInB As Range
    Dim sLetter As String
    Dim sValueInA As String
    Dim iLeftParen As Integer
    Dim iRightParen As Integer
    Dim rCellInC As Range
    Dim sOldValueInC As String
    Dim sNewValueInC As String
    Dim iLen As Integer

    ' Set reference to worksheet
    Set ws = Sheets("S6")
    ' Set reference to range in column B
    Set rColumnB = ws.Range("B7:B14")
    ' Loop through cells
    For Each rCellInB In rColumnB
    ' Check if rCell contains hyphen
    If InStr(rCellInB.Value, "-") > 0 Then
    ' Get value in column A
    sValueInA = rCellInB.Offset(0, -1).Value
    ' Find position of left parenthesis (
    iLeftParen = InStr(sValueInA, "(")
    ' Find position of right parenthesis )
    iRightParen = InStr(iLeftParen + 1, sValueInA, ")")
    ' Get the text between the parentheses
    sLetter = Mid(sValueInA, iLeftParen + 1, iRightParen - iLeftParen - 1)
    ' Loop through C7:C14
    For Each rCellInC In rColumnB.Offset(0, 1)
    ' Get value in column C
    sOldValueInC = rCellInC.Value
    ' Put commas before and after it
    sNewValueInC = "," & rCellInC.Value & ","
    ' Replace comma+letter+comma by comma
    ' Excel 97 doesn't have the Replace function
    ' In XP, Replace is probably faster than the Substitute worksheet function
    sNewValueInC = Application.WorksheetFunction.Substitute(sNewValue InC, "," & sLetter & ",", ",")
    ' Get length of remaining string
    iLen = Len(sNewValueInC)
    ' Avoid error if only one comma left
    If iLen = 1 Then
    iLen = 2
    End If
    ' Get rid of leading and trailing comma
    sNewValueInC = Mid(sNewValueInC, 2, iLen - 2)
    ' Check if value has changed
    If sNewValueInC <> sOldValueInC Then
    ' If so, store new value
    rCellInC.Value = sNewValueInC
    End If
    Next rCellInC
    End If
    Next rCellInB
    End Sub

  4. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,859
    Thanks
    0
    Thanked 178 Times in 164 Posts

    Re: Searching for and deleting letter (Excel xp)

    Hi jha,

    The attached spreadsheet shows another way to skin the cat, without resorting to a macro. You may find this more flexible.

    What I've done is to test for the "- " strings on the bdata sheet, using the otherwise unused Columns D, G, J, M and P. Then, on the S6 sheet, I've done some further testing and data manipulation to return only the required strings in Columns C, F, I, L and O. This approach has simplified the formla coding, since the strings in brackets in Column A on the S6 sheet were the same as the strings on the bdata sheet. If you check column A on the S6 sheet, you'll also note how I've changed your hard-coded values to formula-driven ones, based on the Row# and the corresponding strings from the bdata sheet.

    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    551
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for and deleting letter (Excel xp)

    Thank you so much.

    Just a question. In this formula

    =SUBSTITUTE(SUBSTITUTE(C33,IF(ISERROR((INDEX($A$32 :$A$39,MATCH("- ",OFFSET(B$32:B$39,MATCH("- ",B$32:B$39,0),),0)+MATCH("- ",B$32:B$39,0)))),"",INDEX($A$32:$A$39,MATCH("- ",OFFSET(B$32:B$39,MATCH("- ",B$32:B$39,0),),0)+MATCH("- ",B$32:B$39,0))),""),(INDEX($A$32:$A$39,MATCH( "- ",B$32:B$39,0))),"")

    why do you have to have so many MATCH functions? do you need one for each letter that may occur in the preceding column?

    Also, in this formula

    =IF(ISNA(bdata!F36),"",IF(ISNA(bdata!G36),bdata!F3 6,IF(OR(bdata!G36="0",bdata!G36=","),"",(SUBSTITUT E(TRIM(SUBSTITUTE(bdata!G36,","," "))," ",",")))))

    is the trim function to get rid of multiple commas?

    Thank you for the explanation. I want to understand how to do this so i can use these functions effectively.

  6. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,859
    Thanks
    0
    Thanked 178 Times in 164 Posts

    Re: Searching for and deleting letter (Excel xp)

    Hi JHA,

    I've only just returned from holidays. Hence the delay in replying. In answer to your questions:

    The first formula uses the index/match arguments firstly to test whether the hyphen substitution generates an error (ie no hyphen found) and, then to determine what to do if any are found. Because of this, the index/match formulae are effectively repeated - once to test for the error and once to generate a result if there is no error. It was also necessary to allow for the fact that you might have more than one substitution.

    As you supposed, the second formula uses Trim (in combination with Substitute) to eliminate multiple commas. Trim works with spaces, so one Substitute formula is used to first convert all commas to spaces. Trim then cleans up any repeated spaces, reducing them to single spaces. It also eliminates leading/trailing spaces. Then another substitute changes the single spaces back to commas.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    551
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for and deleting letter (Excel xp)

    ok, thank you very much for the explanation.

Posting Permissions

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