macro that strips data from string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Calop - 17 Sep 2006 18:21 GMT

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

Reply to this Message

Bob Phillips - 17 Sep 2006 19:47 GMT

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

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi,
> I am new to VBA & Macros. Appreciate any help I can get. I have a macro that
[quoted text clipped - 33 lines]
> Thanks,
> Calop

Reply to this Message

Calop - 17 Sep 2006 20:50 GMT

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

> Sub AREA_REPORT()
> Dim oCell As Range
[quoted text clipped - 55 lines]
>> Thanks,
>> Calop

Reply to this Message

Bob Phillips - 17 Sep 2006 21:29 GMT

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

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> 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
[quoted text clipped - 59 lines]
> >> Thanks,
> >> Calop

Reply to this Message

Calop - 17 Sep 2006 21:53 GMT

Thank you for your expertise. It works like a charm. This was my first
experience with a newsgroup, and I am totally impressed.
Calop

> Sub AREA_REPORT()
> Dim oCell As Range
[quoted text clipped - 99 lines]
>> >> Thanks,
>> >> Calop

Reply to this Message

Bob Phillips - 18 Sep 2006 01:35 GMT

Great. Hope to see you again.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Thank you for your expertise. It works like a charm. This was my first
> experience with a newsgroup, and I am totally impressed.
[quoted text clipped - 102 lines]
> >> >> Thanks,
> >> >> Calop

Reply to this Message

Rate this thread: