Results 1 to 2 of 2

Thread: VBA Code to Remove Larger Values between Two Delimiters

  1. #1

    Question VBA Code to Remove Larger Values between Two Delimiters

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a set of data in column A that looks like this:

    White:0:0|Counter Height:0:0|Orange:40:0|Counter Height:40:0|Green:40:0|Counter Height:40:0|White:40:0|Bar Height:40:0|Orange:80:0|Bar Height:80:0|Green:80:0|Bar Height:80:0|

    The delimiter "|" separates the unique values of an item (in this example they are dining stools of differing heights and colors), I need to only keep the lower value and remove the larger one.

    The example above contains both White:0:0 and White:40:0 as two different values associated with the same dining stool. I need to only have White:0:0 remain and White:40:0 to go away.

    Tried various methods but nothing seems to work. Any help is appreciated.


  2. #2
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Try this...

    Sub KeepFirst()
    Dim rMyRng As Range, r As Range, sSep As String, iPos As Integer
    sSep = "|"
    Set rMyRng = Application.InputBox("Select the Range", "Range Input Req.", , , , , , 8)
    For Each r In rMyRng
        iPos = InStr(1, r.Value, sSep)
        If iPos Then r.Value = Left(r.Value, iPos - 1)
    Next r
    End Sub

Posting Permissions

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