Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts

    Deleting a Date Substring Occurring in a Range (97, other)

    Folks, I am trying to create a VBA procedure that will search a selected range of cells and, where it finds any date in the format "mm/dd", it will delete just that substring and its trailing space from the string.

    The starting position of the date can vary, and the dates vary. Examples of the cells:
    TARGET TOWSON 01/03 #000968217 TARGET-TOWSON TOWSON MD
    MARS PADONIA VILL 01/13 #000057822 PADONIA VILL TIMONIUM MD
    GIANT FOOD 01/02 #000315929 LUTHERVILLE MD
    TOYS R US 01/03 #000683480 US TOWSON MD
    PETCO #0940 01/03 #000322971 #0940 LUTHERVILLE MD
    Soooo... in the first cell, I would want to delete "01/03_" where "_" is a space character.

    I am trying to avoid the more obvious, clumsy solution of looping in a large range of possible dates, like 01/01 to 06/30.

    If you have no other ideas, then I will go to the clumsy way.
    Thank you in advance! <img src=/S/please.gif border=0 alt=please width=31 height=23>

  2. #2
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Deleting a Date Substring Occurring in a Range (97, other)

    Hi John,

    I am no expert on VBA but can't you search for a string <number><number>/<number><number>. Because it seems to me that this combination is unique in your data.

    Another idea could be in using a combination of the functions REPLACE FIND and other text functions. Use find to locate the position of the "/" Since this character seems to be unique on his own.

    I hope this will help you some.

    Grtx Forbaty

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting a Date Substring Occurring in a Range (97, other)

    You don't really need VBA, just use Edit, Replace and replace ??/??_ with nothing, where _ is a space.
    In vba, this would be
    <pre> Selection.Replace What:="??/?? ", Replacement:="", LookAt:=xlPart</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Deleting a Date Substring Occurring in a Range (97, other)

    A little dangerous as it would replace mm/dd as well as 01/20.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Deleting a Date Substring Occurring in a Range (97, other)

    if it's a one time thing I'd use a formula, but if not, test this:

    Sub simpkinsdeldatetext()
    Dim rngCell As Range
    Dim intSlashPos As Integer
    Dim strContents As String
    For Each rngCell In Selection.Cells
    strContents = rngCell.Value
    intSlashPos = InStr(strContents, "/")
    If intSlashPos Then
    On Error Resume Next
    If IsDate(CDate(Mid(strContents, intSlashPos - 2, 5))) Then _
    rngCell.Value = Left(strContents, intSlashPos - 3) & _
    Right(strContents, Len(strContents) - intSlashPos - 3)
    End If
    Next rngCell
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts

    Re: Deleting a Date Substring Occurring in a Range (97, other)

    JohnBF:
    Many thanks, m'yte, it looks as though it will do exactly what I need. I'll try it out.
    <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15> <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

  7. #7
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts

    Re: Deleting a Date Substring Occurring in a Range (97, other)

    Thanks very much, Sam! I had overlooked the Replace command, no-VBA thing. When I run into a one-time case, I will now remember to use it.

Posting Permissions

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