macro that strips data from string

  • Thread starter Thread starter Calop
  • Start date Start date
C

Calop

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
 
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)
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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)

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
Click to expand...
Click to expand...
Click to expand...
 
Great. Hope to see you again.

--
HTH

Bob Phillips

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

Calop said:
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 said:
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 said:
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
Click to expand...
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
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)

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top