macro that strips data from string

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
 
B

Bob Phillips

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)
 
C

Calop

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...
 
B

Bob Phillips

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...
 
C

Calop

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...
 
B

Bob Phillips

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find and replace loop based on dates 0
find and replace loop in VBA 0
Word Macro to Clean Up SQL 1
Need advice on macro 1
Sorting comma strings 2
Find and Replace Macros for formatting 1
Word Stop a loop when it reaches the end of the "Word 2003" document 0
Find All Words in Word 2003 2

Top