A bit of A challenge for me to do.

S

Steved

Hello From Steved

4.29x5x Band Of Colours
Using the below Macro will return
Band Of Colours Which is what I want

4.Band Of Colours
Using the below Macro will return
Of Colors missing the word Band

Please what do I need to do to the macro to allways give
me Band Of Colours

Dim s As String
Dim p As Integer
Selection.Paragraphs(1).Range.Select
s = Selection.Text
p = InStr(1, s, " ")
p = InStr(p + 1, s, " ")
s = Right(s, Len(s) - p)
Selection.Paragraphs(1).Range.Text = s

Thankyou.
 
J

Jezebel

You'll need to explain things a little more clearly. Is this a generic task,
or does the sentence always contain "band of colours" or some other oblique
reference to Sherlock Holmes?
 
S

Steved

Hello Jezebel from Steved

Every second line is what I trying to do with the macro

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

2. 4349 Coup Hogan (4) 2g ch
Coup Hogan (4) 2g ch

3. 3. The Guttersnipe (7) 2g gr
The Guttersnipe (7) 2g gr

1. 23 Aim To Score (6) 4g ch
Aim To Score (6) 4g ch

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

Thankyou.
 
J

Jezebel

Appears to be exactly the same as the problem you posted last week: delete
everything up to and including the second space in each line. As suggested
then, use a regular expression.
 
S

Steved

Below Jezebel is what you done for me and I am very
grateful, but using the below Deletes the word Band.

On all others I use your find and replace it is perfect.

Find: [! ]@ [! ]@ ([!^013]@[^013])
Replace with: \1

4. Band Of Colours (AUS) (2) 2g b
Of Colours (AUS) (2) 2g b

Hopefully you now see my issue.
 
J

Jezebel

Of course. The spec was to delete up to and including the second space,
which in this case comes after the word 'Band' -- that's the issue with
regular expressions ... the text you're processing has to be, by definition,
regular in some specifiable sense. Hence my subsequent question ... what is
the regular element in the strings you are checking?





Steved said:
Below Jezebel is what you done for me and I am very
grateful, but using the below Deletes the word Band.

On all others I use your find and replace it is perfect.

Find: [! ]@ [! ]@ ([!^013]@[^013])
Replace with: \1

4. Band Of Colours (AUS) (2) 2g b
Of Colours (AUS) (2) 2g b

Hopefully you now see my issue.


-----Original Message-----
Appears to be exactly the same as the problem you posted last week: delete
everything up to and including the second space in each line. As suggested
then, use a regular expression.







.
Click to expand...
Click to expand...
 
S

Steved

Hello Jezebe from Steved

The criteria I require is delete everything up to the
first word, as in examples below.

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

4. Band Of Colours (AUS) (2) 2g b
Band Of Colours (AUS) (2) 2g b

Thankyou.

-----Original Message-----
Of course. The spec was to delete up to and including the second space,
which in this case comes after the word 'Band' -- that's the issue with
regular expressions ... the text you're processing has to be, by definition,
regular in some specifiable sense. Hence my subsequent question ... what is
the regular element in the strings you are checking?





Below Jezebel is what you done for me and I am very
grateful, but using the below Deletes the word Band.

On all others I use your find and replace it is perfect.

Find: [! ]@ [! ]@ ([!^013]@[^013])
Replace with: \1
Hopefully you now see my issue.


-----Original Message-----
Appears to be exactly the same as the problem you posted last week: delete
everything up to and including the second space in each line. As suggested
then, use a regular expression.




Hello Jezebel from Steved

Every second line is what I trying to do with the macro

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

2. 4349 Coup Hogan (4) 2g ch
Coup Hogan (4) 2g ch

3. 3. The Guttersnipe (7) 2g gr
The Guttersnipe (7) 2g gr

1. 23 Aim To Score (6) 4g ch
Aim To Score (6) 4g ch

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

Thankyou.





-----Original Message-----
You'll need to explain things a little more clearly. Is
this a generic task,
or does the sentence always contain "band of colours"
or some other oblique
reference to Sherlock Holmes?



message
It might look similar but it is not the sme question.

Thankyou.

-----Original Message-----
Answered last week in another forum.



message
Hello From Steved

4.29x5x Band Of Colours
Using the below Macro will return
Band Of Colours Which is what I want

4.Band Of Colours
Using the below Macro will return
Of Colors missing the word Band

Please what do I need to do to the macro to allways
give
me Band Of Colours

Dim s As String
Dim p As Integer
Selection.Paragraphs(1).Range.Select
s = Selection.Text
p = InStr(1, s, " ")
p = InStr(p + 1, s, " ")
s = Right(s, Len(s) - p)
Selection.Paragraphs(1).Range.Text = s

Thankyou.


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
C

Chuck

Please forgive me for jumping in here...

As Jezebel said, in order to delete material from a string you need to
establish what the rules are for deletion. In this case, there don't seem to
be any consistent string-related rules (you can't delete up to the second
<space> for example because some strings only have one <space> before the
material to be retained).

If it's correct that the material you want to keep ALWAYS begins with a
capital letter A-Z, then you could use VBA (not wildcard search) to increment
through the string from left to right to establish the position of the first
instance of a capital letter, then trim off everything to the
left of that position.

There may be other solutions but that's the only one that strikes me having
read this thread.

Steved said:
Hello Jezebe from Steved

The criteria I require is delete everything up to the
first word, as in examples below.

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

4. Band Of Colours (AUS) (2) 2g b
Band Of Colours (AUS) (2) 2g b

Thankyou.

-----Original Message-----
Of course. The spec was to delete up to and including the second space,
which in this case comes after the word 'Band' -- that's the issue with
regular expressions ... the text you're processing has to be, by definition,
regular in some specifiable sense. Hence my subsequent question ... what is
the regular element in the strings you are checking?





Below Jezebel is what you done for me and I am very
grateful, but using the below Deletes the word Band.

On all others I use your find and replace it is perfect.

Find: [! ]@ [! ]@ ([!^013]@[^013])
Replace with: \1


Hopefully you now see my issue.



-----Original Message-----
Appears to be exactly the same as the problem you posted
last week: delete
everything up to and including the second space in each
line. As suggested
then, use a regular expression.




message
Hello Jezebel from Steved

Every second line is what I trying to do with the macro

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

2. 4349 Coup Hogan (4) 2g ch
Coup Hogan (4) 2g ch

3. 3. The Guttersnipe (7) 2g gr
The Guttersnipe (7) 2g gr

1. 23 Aim To Score (6) 4g ch
Aim To Score (6) 4g ch

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

Thankyou.





-----Original Message-----
You'll need to explain things a little more clearly. Is
this a generic task,
or does the sentence always contain "band of colours"
or some other oblique
reference to Sherlock Holmes?



message
It might look similar but it is not the sme question.

Thankyou.

-----Original Message-----
Answered last week in another forum.



in
message
Hello From Steved

4.29x5x Band Of Colours
Using the below Macro will return
Band Of Colours Which is what I want

4.Band Of Colours
Using the below Macro will return
Of Colors missing the word Band

Please what do I need to do to the macro to allways
give
me Band Of Colours

Dim s As String
Dim p As Integer
Selection.Paragraphs(1).Range.Select
s = Selection.Text
p = InStr(1, s, " ")
p = InStr(p + 1, s, " ")
s = Right(s, Len(s) - p)
Selection.Paragraphs(1).Range.Text = s

Thankyou.


.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
S

Steved

Hello Chuck from Steved

Thankyou for your reply

So Is it not possible to write a VBA to search for the
first word in a String or if that is possible I would be
happy.

Cheers.


-----Original Message-----
Please forgive me for jumping in here...

As Jezebel said, in order to delete material from a string you need to
establish what the rules are for deletion. In this case, there don't seem to
be any consistent string-related rules (you can't delete up to the second
<space> for example because some strings only have one
Click to expand...
material to be retained).

If it's correct that the material you want to keep ALWAYS begins with a
capital letter A-Z, then you could use VBA (not wildcard search) to increment
through the string from left to right to establish the position of the first
instance of a capital letter, then trim off everything to the
left of that position.

There may be other solutions but that's the only one that strikes me having
read this thread.

Steved said:
Hello Jezebe from Steved

The criteria I require is delete everything up to the
first word, as in examples below.

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

4. Band Of Colours (AUS) (2) 2g b
Band Of Colours (AUS) (2) 2g b

Thankyou.

-----Original Message-----
Of course. The spec was to delete up to and including
Click to expand...
the
second space,
which in this case comes after the word 'Band' --
Click to expand...
that's
the issue with
regular expressions ... the text you're processing has
Click to expand...
to
be, by definition,
regular in some specifiable sense. Hence my subsequent question ... what is
the regular element in the strings you are checking?





"Steved" <[email protected]> wrote
Click to expand...
in
message
Below Jezebel is what you done for me and I am very
grateful, but using the below Deletes the word Band.

On all others I use your find and replace it is perfect.

Find: [! ]@ [! ]@ ([!^013]@[^013])
Replace with: \1


Hopefully you now see my issue.



-----Original Message-----
Appears to be exactly the same as the problem you posted
last week: delete
everything up to and including the second space in each
line. As suggested
then, use a regular expression.




message
Hello Jezebel from Steved

Every second line is what I trying to do with the macro

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

2. 4349 Coup Hogan (4) 2g ch
Coup Hogan (4) 2g ch

3. 3. The Guttersnipe (7) 2g gr
The Guttersnipe (7) 2g gr

1. 23 Aim To Score (6) 4g ch
Aim To Score (6) 4g ch

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

Thankyou.





-----Original Message-----
You'll need to explain things a little more
Click to expand...
clearly.
Is
this a generic task,
or does the sentence always contain "band of colours"
or some other oblique
reference to Sherlock Holmes?



"Steved" <[email protected]>
Click to expand...
wrote
in
message
It might look similar but it is not the sme question.

Thankyou.

-----Original Message-----
Answered last week in another forum.



in
message
Hello From Steved

4.29x5x Band Of Colours
Using the below Macro will return
Band Of Colours Which is what I want

4.Band Of Colours
Using the below Macro will return
Of Colors missing the word Band

Please what do I need to do to the macro to allways
give
me Band Of Colours

Dim s As String
Dim p As Integer
Selection.Paragraphs(1).Range.Select
s = Selection.Text
p = InStr(1, s, " ")
p = InStr(p + 1, s, " ")
s = Right(s, Len(s) - p)
Selection.Paragraphs(1).Range.Text = s

Thankyou.


.



.



.



.
Click to expand...
Click to expand...
.
Click to expand...
 
J

Jezebel

You need to define 'word' in this context. If you mean a string beginning
with a capital letter, or comprising letters only, then Find and Replace
will do it, as will VBA. Here's another VBA --


Dim pWords() as string
pWords = Split(Range.Text, " ")
For i = 0 to ubound(pWords)
if pWords(i) like "[a-zA-Z]{1,}" then
pStartWord = i
exit for
end if
Next

Output = pWords(pStartWord)
For i = pStartWord + 1 to ubound(pWords)
Output = Output & " " & pWords(i)
Next





Steved said:
Hello Chuck from Steved

Thankyou for your reply

So Is it not possible to write a VBA to search for the
first word in a String or if that is possible I would be
happy.

Cheers.


-----Original Message-----
Please forgive me for jumping in here...

As Jezebel said, in order to delete material from a string you need to
establish what the rules are for deletion. In this case, there don't seem to
be any consistent string-related rules (you can't delete up to the second
<space> for example because some strings only have one
Click to expand...
material to be retained).

If it's correct that the material you want to keep ALWAYS begins with a
capital letter A-Z, then you could use VBA (not wildcard search) to increment
through the string from left to right to establish the position of the first
instance of a capital letter, then trim off everything to the
left of that position.

There may be other solutions but that's the only one that strikes me having
read this thread.

Steved said:
Hello Jezebe from Steved

The criteria I require is delete everything up to the
first word, as in examples below.

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

4. Band Of Colours (AUS) (2) 2g b
Band Of Colours (AUS) (2) 2g b

Thankyou.


-----Original Message-----
Of course. The spec was to delete up to and including the
second space,
which in this case comes after the word 'Band' -- that's
the issue with
regular expressions ... the text you're processing has to
be, by definition,
regular in some specifiable sense. Hence my subsequent
question ... what is
the regular element in the strings you are checking?





message
Below Jezebel is what you done for me and I am very
grateful, but using the below Deletes the word Band.

On all others I use your find and replace it is perfect.

Find: [! ]@ [! ]@ ([!^013]@[^013])
Replace with: \1


Hopefully you now see my issue.



-----Original Message-----
Appears to be exactly the same as the problem you posted
last week: delete
everything up to and including the second space in each
line. As suggested
then, use a regular expression.




message
Hello Jezebel from Steved

Every second line is what I trying to do with the
macro

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

2. 4349 Coup Hogan (4) 2g ch
Coup Hogan (4) 2g ch

3. 3. The Guttersnipe (7) 2g gr
The Guttersnipe (7) 2g gr

1. 23 Aim To Score (6) 4g ch
Aim To Score (6) 4g ch

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

Thankyou.





-----Original Message-----
You'll need to explain things a little more clearly.
Is
this a generic task,
or does the sentence always contain "band of colours"
or some other oblique
reference to Sherlock Holmes?



in
message
It might look similar but it is not the sme
question.

Thankyou.

-----Original Message-----
Answered last week in another forum.



in
message
Hello From Steved

4.29x5x Band Of Colours
Using the below Macro will return
Band Of Colours Which is what I want

4.Band Of Colours
Using the below Macro will return
Of Colors missing the word Band

Please what do I need to do to the macro to
allways
give
me Band Of Colours

Dim s As String
Dim p As Integer
Selection.Paragraphs(1).Range.Select
s = Selection.Text
p = InStr(1, s, " ")
p = InStr(p + 1, s, " ")
s = Right(s, Len(s) - p)
Selection.Paragraphs(1).Range.Text = s

Thankyou.


.



.



.



.
Click to expand...
.
Click to expand...
Click to expand...
 
S

Steved

Hello Jezebel from Steved

Jezebel as you would have quessed and that is that
I am not very good at explaning my issue here.

I would like to thankyou for your patience.

Cheers.
-----Original Message-----
You need to define 'word' in this context. If you mean a string beginning
with a capital letter, or comprising letters only, then Find and Replace
will do it, as will VBA. Here's another VBA --


Dim pWords() as string
pWords = Split(Range.Text, " ")
For i = 0 to ubound(pWords)
if pWords(i) like "[a-zA-Z]{1,}" then
pStartWord = i
exit for
end if
Next

Output = pWords(pStartWord)
For i = pStartWord + 1 to ubound(pWords)
Output = Output & " " & pWords(i)
Next





Hello Chuck from Steved

Thankyou for your reply

So Is it not possible to write a VBA to search for the
first word in a String or if that is possible I would be
happy.

Cheers.


-----Original Message-----
Please forgive me for jumping in here...

As Jezebel said, in order to delete material from a string you need to
establish what the rules are for deletion. In this
Click to expand...
case,
there don't seem to
be any consistent string-related rules (you can't delete up to the second
<space> for example because some strings only have one
Click to expand...
material to be retained).

If it's correct that the material you want to keep
Click to expand...
ALWAYS
begins with a
capital letter A-Z, then you could use VBA (not wildcard search) to increment
through the string from left to right to establish the position of the first
instance of a capital letter, then trim off everything
Click to expand...
to
the
left of that position.

There may be other solutions but that's the only one
Click to expand...
that
strikes me having
read this thread.

:

Hello Jezebe from Steved

The criteria I require is delete everything up to the
first word, as in examples below.

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

4. Band Of Colours (AUS) (2) 2g b
Band Of Colours (AUS) (2) 2g b

Thankyou.


-----Original Message-----
Of course. The spec was to delete up to and including the
second space,
which in this case comes after the word 'Band' -- that's
the issue with
regular expressions ... the text you're processing
Click to expand...
has
to
be, by definition,
regular in some specifiable sense. Hence my subsequent
question ... what is
the regular element in the strings you are checking?





message
Below Jezebel is what you done for me and I am very
grateful, but using the below Deletes the word Band.

On all others I use your find and replace it is perfect.

Find: [! ]@ [! ]@ ([!^013]@[^013])
Replace with: \1


Hopefully you now see my issue.



-----Original Message-----
Appears to be exactly the same as the problem you posted
last week: delete
everything up to and including the second space in each
line. As suggested
then, use a regular expression.




"Steved" <[email protected]>
Click to expand...
wrote
in
message
Hello Jezebel from Steved

Every second line is what I trying to do with the
macro

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

2. 4349 Coup Hogan (4) 2g ch
Coup Hogan (4) 2g ch

3. 3. The Guttersnipe (7) 2g gr
The Guttersnipe (7) 2g gr

1. 23 Aim To Score (6) 4g ch
Aim To Score (6) 4g ch

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

Thankyou.





-----Original Message-----
You'll need to explain things a little more clearly.
Is
this a generic task,
or does the sentence always contain "band of colours"
or some other oblique
reference to Sherlock Holmes?



in
message
It might look similar but it is not the sme
question.

Thankyou.

-----Original Message-----
Answered last week in another forum.



in
message
Hello From Steved

4.29x5x Band Of Colours
Using the below Macro will return
Band Of Colours Which is what I want

4.Band Of Colours
Using the below Macro will return
Of Colors missing the word Band

Please what do I need to do to the macro to
allways
give
me Band Of Colours

Dim s As String
Dim p As Integer
Selection.Paragraphs(1).Range.Select
s = Selection.Text
p = InStr(1, s, " ")
p = InStr(p + 1, s, " ")
s = Right(s, Len(s) - p)
Selection.Paragraphs(1).Range.Text = s

Thankyou.


.



.



.



.


.
Click to expand...
Click to expand...


.
Click to expand...
 
C

Chuck

Hi Jezebel

When I tested your code it worked fine except that it kept returning the
original string as Output. However if I amended the line containing the Like
operator it worked fine, eg:

Replace: if pWords(i) like "[a-zA-Z]{1,}" then

with: if pWords(i) like "[a-zA-Z]*" then

Unless I'm missing something?

I also took the liberty of making a Function from your code:

Function StripStringToWords(sString As String)

Dim pWords() As String
Dim Output As String
Dim i As Long
Dim pStartWord As Long

pWords = Split(sString, " ")
For i = 0 To UBound(pWords)
If pWords(i) Like "[a-zA-Z]*" Then
pStartWord = i
Exit For
End If
Next

Output = pWords(pStartWord)
For i = pStartWord + 1 To UBound(pWords)
Output = Output & " " & pWords(i)
Next

StripStringToWords = Output

End Function

Chuck

Jezebel said:
You need to define 'word' in this context. If you mean a string beginning
with a capital letter, or comprising letters only, then Find and Replace
will do it, as will VBA. Here's another VBA --


Dim pWords() as string
pWords = Split(Range.Text, " ")
For i = 0 to ubound(pWords)
if pWords(i) like "[a-zA-Z]{1,}" then
pStartWord = i
exit for
end if
Next

Output = pWords(pStartWord)
For i = pStartWord + 1 to ubound(pWords)
Output = Output & " " & pWords(i)
Next





Steved said:
Hello Chuck from Steved

Thankyou for your reply

So Is it not possible to write a VBA to search for the
first word in a String or if that is possible I would be
happy.

Cheers.


-----Original Message-----
Please forgive me for jumping in here...

As Jezebel said, in order to delete material from a string you need to
establish what the rules are for deletion. In this case, there don't seem to
be any consistent string-related rules (you can't delete up to the second
<space> for example because some strings only have one
Click to expand...
material to be retained).

If it's correct that the material you want to keep ALWAYS begins with a
capital letter A-Z, then you could use VBA (not wildcard search) to increment
through the string from left to right to establish the position of the first
instance of a capital letter, then trim off everything to the
left of that position.

There may be other solutions but that's the only one that strikes me having
read this thread.

:

Hello Jezebe from Steved

The criteria I require is delete everything up to the
first word, as in examples below.

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

4. Band Of Colours (AUS) (2) 2g b
Band Of Colours (AUS) (2) 2g b

Thankyou.


-----Original Message-----
Of course. The spec was to delete up to and including the
second space,
which in this case comes after the word 'Band' -- that's
the issue with
regular expressions ... the text you're processing has to
be, by definition,
regular in some specifiable sense. Hence my subsequent
question ... what is
the regular element in the strings you are checking?





message
Below Jezebel is what you done for me and I am very
grateful, but using the below Deletes the word Band.

On all others I use your find and replace it is perfect.

Find: [! ]@ [! ]@ ([!^013]@[^013])
Replace with: \1


Hopefully you now see my issue.



-----Original Message-----
Appears to be exactly the same as the problem you posted
last week: delete
everything up to and including the second space in each
line. As suggested
then, use a regular expression.




message
Hello Jezebel from Steved

Every second line is what I trying to do with the
macro

1. 17138 Drovers Call (6) 2g b
Drovers Call (6) 2g b

2. 4349 Coup Hogan (4) 2g ch
Coup Hogan (4) 2g ch

3. 3. The Guttersnipe (7) 2g gr
The Guttersnipe (7) 2g gr

1. 23 Aim To Score (6) 4g ch
Aim To Score (6) 4g ch

3. 29x5x The Oracle (4) 5g br
The Oracle (4) 5g br

Thankyou.





-----Original Message-----
You'll need to explain things a little more clearly.
Is
this a generic task,
or does the sentence always contain "band of colours"
or some other oblique
reference to Sherlock Holmes?



in
message
It might look similar but it is not the sme
question.

Thankyou.

-----Original Message-----
Answered last week in another forum.



in
message
Hello From Steved

4.29x5x Band Of Colours
Using the below Macro will return
Band Of Colours Which is what I want

4.Band Of Colours
Using the below Macro will return
Of Colors missing the word Band

Please what do I need to do to the macro to
allways
give
me Band Of Colours

Dim s As String
Dim p As Integer
Selection.Paragraphs(1).Range.Select
s = Selection.Text
p = InStr(1, s, " ")
p = InStr(p + 1, s, " ")
s = Right(s, Len(s) - p)
Selection.Paragraphs(1).Range.Text = s

Thankyou.


.



.



.



.


.
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

VBA Coding Help for Beginner 0
Need help modifying code 0
To run threw the whole document 2
To goto end of document 2
Editing Footnotes 5
Pasting an image using MACROS 0
Message Boxes do not work properly in search and replace macro. 17
type mismatch error in macro in excel 0

Top