Thank you for your expertise. It works like a charm. This was my first
experience with a newsgroup, and I am totally impressed.
Calop
"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sub AREA_REPORT()
> Dim oCell As Range
> Dim tmp As String
> Dim iRow As Long
> Dim sFirst As String
>
> Set oCell = Selection.Find("*-*(*")
> If Not oCell Is Nothing Then
> iRow = iRow + 1
> Cells(iRow, "B").Value = CheckData(oCell)
> sFirst = oCell.Address
> Do
> Set oCell = Selection.FindNext(oCell)
> If Not oCell Is Nothing Then
> If oCell.Address <> sFirst Then
> iRow = iRow + 1
> Cells(iRow, "B").Value = CheckData(oCell)
> End If
> End If
> Loop While Not oCell Is Nothing And oCell.Address <> sFirst
> End If
>
> End Sub
>
> Private Function CheckData(cell As Range)
> Dim iPos1 As Long
> Dim iPos2 As Long
>
> iPos1 = InStr(cell.Value, "-")
> iPos1 = InStr(iPos1 + 1, cell.Value, "-")
> iPos2 = InStr(iPos2 + 1, cell.Value, "(")
> CheckData = Left(cell.Value, iPos1 - 1) & _
> Right(cell.Value, Len(cell.Value) - iPos2 + 1)
>
> End Function
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Calop" <(E-Mail Removed)> wrote in message
> news:zGhPg.26$(E-Mail Removed)...
>> Bob, Thank you for the quick response. The macro works good but at the
> end
>> of the string I need the (In) to look like this. Also if there are 50 to
> 100
>> rows in column A, what do I put to strip all at once. Again, thank you.
>> Calop
>> "Bob Phillips" <(E-Mail Removed)> wrote in message
>> news:%23g$(E-Mail Removed)...
>> > Sub AREA_REPORT()
>> > Dim oCell As Range
>> > Dim iPos1 As Long
>> > Dim iPos2 As Long
>> >
>> > Set oCell = Selection.Find("*-*(*")
>> > If Not oCell Is Nothing Then
>> > iPos1 = InStr(oCell.Value, "-")
>> > If iPos1 > 0 Then
>> > iPos1 = InStr(iPos1 + 1, oCell.Value, "-")
>> > If iPos1 > 0 Then
>> > iPos2 = InStr(iPos2 + 1, oCell.Value, "(")
>> > Range("B1").Value = Left(oCell.Value, iPos1 - 1) & _
>> > Right(oCell.Value, Len(oCell.Value) - iPos2)
>> > End If
>> > End If
>> > End If
>> >
>> > End Sub
>> >
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (replace somewhere in email address with gmail if mailing direct)
>> >
>> > "Calop" <(E-Mail Removed)> wrote in message
>> > news:JufPg.40$(E-Mail Removed)...
>> >> Hi,
>> >> I am new to VBA & Macros. Appreciate any help I can get. I have a
>> >> macro
>> > that
>> >> strip's data from a string and it works fine except I need it to strip
>> >> further in the string. example of results:
>> >> bunder_linn-S1/0-720L-140.55.135.34-NIPR-1.5M(In)
>> >>
>> >> What I need is to strip from the second - to the (
>> >> example: bunder_linn-S1/0-720L-140.55.135.34-NIPR-1.5M(In)
>> >>
>> >>
>> >> It can strip and copy to clipboard or to cell b1. Either way will
>> >> work.
>> > This
>> >> macro was in a word doc, will it work in excel also?
>> >>
>> >> Sub AREA_REPORT()
>> >> '
>> >> ' AREA_REPORT Macro
>> >> '
>> >> Selection.Find.ClearFormatting
>> >> Selection.Find.Replacement.ClearFormatting
>> >> With Selection.Find
>> >> .Text = "[-]*[(]"
>> >> .Replacement.Text = " ("
>> >> .Forward = True
>> >> .Wrap = wdFindContinue
>> >> .Format = False
>> >> .MatchCase = False
>> >> .MatchWholeWord = False
>> >> .MatchAllWordForms = False
>> >> .MatchSoundsLike = False
>> >> .MatchWildcards = True
>> >> End With
>> >> Selection.Find.Execute Replace:=wdReplaceAll
>> >> Selection.WholeStory
>> >> Selection.Cut
>> >> End Sub
>> >>
>> >> Thanks,
>> >> Calop
>> >>
>> >>
>> >
>> >
>>
>>
>
>
|
Similar Threads
