Macro to grab "inbetween words" from a text
 

Group members and MVP's,

I have a text in range("A1"). Let us assume that the text is C:
\012245\Aeroplane\

I would like to have a macro that grabs the word "plane" from the text
C:\012245\Aeroplane\

Recording a macro doesnt seem to help in this case.

Thanks!
-T

Macro to grab "inbetween words" from a text
 

Need more info in order to really help. What is the criteria for the
grabbed text? If all you want is "plane" out of that file you listed,
you could use the Mid function.
These examples assume that the text is in A12.
=MID(A12,15,5)
Or
=RIGHT(SUBSTITUTE(A12,"\",""),5)

Thulasiram wrote:
Group members and MVP's,

I have a text in range("A1"). Let us assume that the text is C:
\012245\Aeroplane\

I would like to have a macro that grabs the word "plane" from the text
C:\012245\Aeroplane\

Recording a macro doesnt seem to help in this case.

Thanks!
-T

Macro to grab "inbetween words" from a text
 

On Sep 5, 9:12 am, JW wrote:
Need more info in order to really help. What is the criteria for the
grabbed text? If all you want is "plane" out of that file you listed,
you could use the Mid function.
These examples assume that the text is in A12.
=MID(A12,15,5)
Or
=RIGHT(SUBSTITUTE(A12,"\",""),5)

Thulasiram wrote:
Group members and MVP's,

I have a text in range("A1"). Let us assume that the text is C:
\012245\Aeroplane\

I would like to have a macro that grabs the word "plane" from the text
C:\012245\Aeroplane\

Recording a macro doesnt seem to help in this case.

Thanks!
-T

Thanks! This what I want.. To actaully suit it should have been
=MID(A1,15,5).

Can you give me the VBA code for this. Suppose if I click a command
button, I want this to happen.

sub grab()
...
...
....
end sub

and then

Private Sub CommandButton1_Click()
call grab
End Sub

Thanks!
-T

Macro to grab "inbetween words" from a text
 

On Sep 5, 9:12 am, JW wrote:
Need more info in order to really help. What is the criteria for the
grabbed text? If all you want is "plane" out of that file you listed,
you could use the Mid function.
These examples assume that the text is in A12.
=MID(A12,15,5)
Or
=RIGHT(SUBSTITUTE(A12,"\",""),5)

Thulasiram wrote:
Group members and MVP's,

I have a text in range("A1"). Let us assume that the text is C:
\012245\Aeroplane\

I would like to have a macro that grabs the word "plane" from the text
C:\012245\Aeroplane\

Recording a macro doesnt seem to help in this case.

Thanks!
-T

Hi Jeff,

The code you have given counts 15 characters from "left to right" and
then takes 5 characters (plane). How to tweak the code if I have to
make it from "right to left" and take the same 5 characters (plane)?
Please help.. Thanks!

Macro to grab "inbetween words" from a text
 

That would require some type of criteria as to where to begin the grab
and many characters to include in the grab.

Thulasiram wrote:
On Sep 5, 9:12 am, JW wrote:
Need more info in order to really help. What is the criteria for the
grabbed text? If all you want is "plane" out of that file you listed,
you could use the Mid function.
These examples assume that the text is in A12.
=MID(A12,15,5)
Or
=RIGHT(SUBSTITUTE(A12,"\",""),5)

Thulasiram wrote:
Group members and MVP's,

I have a text in range("A1"). Let us assume that the text is C:
\012245\Aeroplane\

I would like to have a macro that grabs the word "plane" from the text
C:\012245\Aeroplane\

Recording a macro doesnt seem to help in this case.

Thanks!
-T

Hi Jeff,

The code you have given counts 15 characters from "left to right" and
then takes 5 characters (plane). How to tweak the code if I have to
make it from "right to left" and take the same 5 characters (plane)?
Please help.. Thanks!

Macro to grab "inbetween words" from a text
 

This will place "plane" in B1 if the string you used in your example
is in A1. This is a very harsh way of doing this as the ranges are
hard coded, but that's about all I can do based on the info you have
provided.
Sub grab()
Range("B1").Value = Mid(Range("A1").Value, 15, 5)
End Sub
Thulasiram wrote:
On Sep 5, 9:12 am, JW wrote:
Need more info in order to really help. What is the criteria for the
grabbed text? If all you want is "plane" out of that file you listed,
you could use the Mid function.
These examples assume that the text is in A12.
=MID(A12,15,5)
Or
=RIGHT(SUBSTITUTE(A12,"\",""),5)

Thulasiram wrote:
Group members and MVP's,

I have a text in range("A1"). Let us assume that the text is C:
\012245\Aeroplane\

I would like to have a macro that grabs the word "plane" from the text
C:\012245\Aeroplane\

Recording a macro doesnt seem to help in this case.

Thanks!
-T

Thanks! This what I want.. To actaully suit it should have been
=MID(A1,15,5).

Can you give me the VBA code for this. Suppose if I click a command
button, I want this to happen.

sub grab()
..
..
...
end sub

and then

Private Sub CommandButton1_Click()
call grab
End Sub

Thanks!
-T

Macro to grab "inbetween words" from a text
 

Thanks Jeff. It works. As I asked to you my previous post, the code
you have given counts 15 characters from "left to right" and then
takes 5 characters (plane). How to tweak the code if I have to make it
from "right to left" and take the same 5 characters (plane)?

So, the criteria would be grabbing 5 characters (second to sixth
characters) in the right to left fashion. If the word is Aeroplane\
then the result would be plane. If the word is Elephant, then the
result would be ephan.

-Thulasiram

On Sep 5, 11:56 am, JW wrote:
This will place "plane" in B1 if the string you used in your example
is in A1. This is a very harsh way of doing this as the ranges are
hard coded, but that's about all I can do based on the info you have
provided.
Sub grab()
Range("B1").Value = Mid(Range("A1").Value, 15, 5)
End Sub

Thulasiram wrote:
On Sep 5, 9:12 am, JW wrote:
Need more info in order to really help. What is the criteria for the
grabbed text? If all you want is "plane" out of that file you listed,
you could use the Mid function.
These examples assume that the text is in A12.
=MID(A12,15,5)
Or
=RIGHT(SUBSTITUTE(A12,"\",""),5)

Thulasiram wrote:
Group members and MVP's,

I have a text in range("A1"). Let us assume that the text is C:
\012245\Aeroplane\

I would like to have a macro that grabs the word "plane" from the text
C:\012245\Aeroplane\

Recording a macro doesnt seem to help in this case.

Thanks!
-T

Thanks! This what I want.. To actaully suit it should have been
=MID(A1,15,5).

Can you give me the VBA code for this. Suppose if I click a command
button, I want this to happen.

sub grab()
..
..
...
end sub

and then

Private Sub CommandButton1_Click()
call grab
End Sub

Thanks!
-T

Macro to grab "inbetween words" from a text
 

The below formula and/or sub will do what you want. But what to do if
the value in A1 is less than 5 characters in length?
=MID(A1,LEN(A1)-5,5)
Sub grab()
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value)
- 5, 5)
End Sub

Thulasiram wrote:
Thanks Jeff. It works. As I asked to you my previous post, the code
you have given counts 15 characters from "left to right" and then
takes 5 characters (plane). How to tweak the code if I have to make it
from "right to left" and take the same 5 characters (plane)?

So, the criteria would be grabbing 5 characters (second to sixth
characters) in the right to left fashion. If the word is Aeroplane\
then the result would be plane. If the word is Elephant, then the
result would be ephan.

-Thulasiram

On Sep 5, 11:56 am, JW wrote:
This will place "plane" in B1 if the string you used in your example
is in A1. This is a very harsh way of doing this as the ranges are
hard coded, but that's about all I can do based on the info you have
provided.
Sub grab()
Range("B1").Value = Mid(Range("A1").Value, 15, 5)
End Sub

Thulasiram wrote:
On Sep 5, 9:12 am, JW wrote:
Need more info in order to really help. What is the criteria for the
grabbed text? If all you want is "plane" out of that file you listed,
you could use the Mid function.
These examples assume that the text is in A12.
=MID(A12,15,5)
Or
=RIGHT(SUBSTITUTE(A12,"\",""),5)

Thulasiram wrote:
Group members and MVP's,

I have a text in range("A1"). Let us assume that the text is C:
\012245\Aeroplane\

I would like to have a macro that grabs the word "plane" from the text
C:\012245\Aeroplane\

Recording a macro doesnt seem to help in this case.

Thanks!
-T

Thanks! This what I want.. To actaully suit it should have been
=MID(A1,15,5).

Can you give me the VBA code for this. Suppose if I click a command
button, I want this to happen.

sub grab()
..
..
...
end sub

and then

Private Sub CommandButton1_Click()
call grab
End Sub

Thanks!
-T

Macro to grab "inbetween words" from a text
 

Jeff,
The code works is super cool!

I now have something like this to answer your question:

If Len(Range("A1").Value) < 5 Then
MsgBox "Not possible"
Else
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 5,
5)
End If

and the other possibility can be

If Len(Range("A1").Value) = 5 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 4,
4)
ElseIf Len(Range("A1").Value) = 4 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 3,
3)
ElseIf Len(Range("A1").Value) = 3 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 2,
2)
ElseIf Len(Range("A1").Value) = 2 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 1,
1)
ElseIf Len(Range("A1").Value) 5 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 5,
5)
ElseIf Len(Range("A1").Value) < 2 Then
MsgBox "Not possible"
End If

Do you know an easier way to do this?

Thanks,
Thulasiram

On Sep 6, 6:08 am, JW wrote:
The below formula and/or sub will do what you want. But what to do if
the value in A1 is less than 5 characters in length?
=MID(A1,LEN(A1)-5,5)
Sub grab()
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value)
- 5, 5)
End Sub

Thulasiramwrote:
Thanks Jeff. It works. As I asked to you my previous post, the code
you have given counts 15 characters from "left to right" and then
takes 5 characters (plane). How to tweak the code if I have to make it
from "right to left" and take the same 5 characters (plane)?

So, the criteria would be grabbing 5 characters (second to sixth
characters) in the right to left fashion. If the word is Aeroplane\
then the result would be plane. If the word is Elephant, then the
result would be ephan.

-Thulasiram

On Sep 5, 11:56 am, JW wrote:
This will place "plane" in B1 if the string you used in your example
is in A1. This is a very harsh way of doing this as the ranges are
hard coded, but that's about all I can do based on the info you have
provided.
Sub grab()
Range("B1").Value = Mid(Range("A1").Value, 15, 5)
End Sub

Thulasiramwrote:
On Sep 5, 9:12 am, JW wrote:
Need more info in order to really help. What is the criteria for the
grabbed text? If all you want is "plane" out of that file you listed,
you could use the Mid function.
These examples assume that the text is in A12.
=MID(A12,15,5)
Or
=RIGHT(SUBSTITUTE(A12,"\",""),5)

Thulasiramwrote:
Group members and MVP's,

I have a text in range("A1"). Let us assume that the text is C:
\012245\Aeroplane\

I would like to have a macro that grabs the word "plane" from the text
C:\012245\Aeroplane\

Recording a macro doesnt seem to help in this case.

Thanks!
-T

Thanks! This what I want.. To actaully suit it should have been
=MID(A1,15,5).

Can you give me the VBA code for this. Suppose if I click a command
button, I want this to happen.

sub grab()
..
..
...
end sub

and then

Private Sub CommandButton1_Click()
call grab
End Sub

Thanks!
-T

Macro to grab "inbetween words" from a text
 

Give this a shot.
Dim l As Integer
lgth = Len(Range("A1").Value)
If lgth 5 Then
Range("B1").Value = Mid(Range("A1").Value, lgth - 5, 5)
ElseIf lgth < 2 Then
MsgBox "Not possible"
Else
Range("B1").Value = Mid(Range("A1").Value, lgth - (lgth - 1),
lgth - 1)
End If
Thulasiram wrote:
Jeff,
The code works is super cool!

I now have something like this to answer your question:

If Len(Range("A1").Value) < 5 Then
MsgBox "Not possible"
Else
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 5,
5)
End If

and the other possibility can be

If Len(Range("A1").Value) = 5 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 4,
4)
ElseIf Len(Range("A1").Value) = 4 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 3,
3)
ElseIf Len(Range("A1").Value) = 3 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 2,
2)
ElseIf Len(Range("A1").Value) = 2 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 1,
1)
ElseIf Len(Range("A1").Value) 5 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 5,
5)
ElseIf Len(Range("A1").Value) < 2 Then
MsgBox "Not possible"
End If

Do you know an easier way to do this?

Thanks,
Thulasiram

On Sep 6, 6:08 am, JW wrote:
The below formula and/or sub will do what you want. But what to do if
the value in A1 is less than 5 characters in length?
=MID(A1,LEN(A1)-5,5)
Sub grab()
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value)
- 5, 5)
End Sub

Thulasiramwrote:
Thanks Jeff. It works. As I asked to you my previous post, the code
you have given counts 15 characters from "left to right" and then
takes 5 characters (plane). How to tweak the code if I have to make it
from "right to left" and take the same 5 characters (plane)?

So, the criteria would be grabbing 5 characters (second to sixth
characters) in the right to left fashion. If the word is Aeroplane\
then the result would be plane. If the word is Elephant, then the
result would be ephan.

-Thulasiram

On Sep 5, 11:56 am, JW wrote:
This will place "plane" in B1 if the string you used in your example
is in A1. This is a very harsh way of doing this as the ranges are
hard coded, but that's about all I can do based on the info you have
provided.
Sub grab()
Range("B1").Value = Mid(Range("A1").Value, 15, 5)
End Sub

Thulasiramwrote:
On Sep 5, 9:12 am, JW wrote:
Need more info in order to really help. What is the criteria for the
grabbed text? If all you want is "plane" out of that file you listed,
you could use the Mid function.
These examples assume that the text is in A12.
=MID(A12,15,5)
Or
=RIGHT(SUBSTITUTE(A12,"\",""),5)

Thulasiramwrote:
Group members and MVP's,

I have a text in range("A1"). Let us assume that the text is C:
\012245\Aeroplane\

I would like to have a macro that grabs the word "plane" from the text
C:\012245\Aeroplane\

Recording a macro doesnt seem to help in this case.

Thanks!
-T

Thanks! This what I want.. To actaully suit it should have been
=MID(A1,15,5).

Can you give me the VBA code for this. Suppose if I click a command
button, I want this to happen.

sub grab()
..
..
...
end sub

and then

Private Sub CommandButton1_Click()
call grab
End Sub

Thanks!
-T

Macro to grab "inbetween words" from a text
 

Works great!

On Sep 6, 9:37 am, JW wrote:
Give this a shot.
Dim l As Integer
lgth = Len(Range("A1").Value)
If lgth 5 Then
Range("B1").Value = Mid(Range("A1").Value, lgth - 5, 5)
ElseIf lgth < 2 Then
MsgBox "Not possible"
Else
Range("B1").Value = Mid(Range("A1").Value, lgth - (lgth - 1),
lgth - 1)
End If

Thulasiramwrote:
Jeff,
The code works is super cool!

I now have something like this to answer your question:

If Len(Range("A1").Value) < 5 Then
MsgBox "Not possible"
Else
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 5,
5)
End If

and the other possibility can be

If Len(Range("A1").Value) = 5 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 4,
4)
ElseIf Len(Range("A1").Value) = 4 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 3,
3)
ElseIf Len(Range("A1").Value) = 3 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 2,
2)
ElseIf Len(Range("A1").Value) = 2 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 1,
1)
ElseIf Len(Range("A1").Value) 5 Then
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value) - 5,
5)
ElseIf Len(Range("A1").Value) < 2 Then
MsgBox "Not possible"
End If

Do you know an easier way to do this?

Thanks,
Thulasiram

On Sep 6, 6:08 am, JW wrote:
The below formula and/or sub will do what you want. But what to do if
the value in A1 is less than 5 characters in length?
=MID(A1,LEN(A1)-5,5)
Sub grab()
Range("B1").Value = Mid(Range("A1").Value, Len(Range("A1").Value)
- 5, 5)
End Sub

Thulasiramwrote:
Thanks Jeff. It works. As I asked to you my previous post, the code
you have given counts 15 characters from "left to right" and then
takes 5 characters (plane). How to tweak the code if I have to make it
from "right to left" and take the same 5 characters (plane)?

So, the criteria would be grabbing 5 characters (second to sixth
characters) in the right to left fashion. If the word is Aeroplane\
then the result would be plane. If the word is Elephant, then the
result would be ephan.

-Thulasiram

On Sep 5, 11:56 am, JW wrote:
This will place "plane" in B1 if the string you used in your example
is in A1. This is a very harsh way of doing this as the ranges are
hard coded, but that's about all I can do based on the info you have
provided.
Sub grab()
Range("B1").Value = Mid(Range("A1").Value, 15, 5)
End Sub

Thulasiramwrote:
On Sep 5, 9:12 am, JW wrote:
Need more info in order to really help. What is the criteria for the
grabbed text? If all you want is "plane" out of that file you listed,
you could use the Mid function.
These examples assume that the text is in A12.
=MID(A12,15,5)
Or
=RIGHT(SUBSTITUTE(A12,"\",""),5)

Thulasiramwrote:
Group members and MVP's,

I have a text in range("A1"). Let us assume that the text is C:
\012245\Aeroplane\

I would like to have a macro that grabs the word "plane" from the text
C:\012245\Aeroplane\

Recording a macro doesnt seem to help in this case.

Thanks!
-T

Thanks! This what I want.. To actaully suit it should have been
=MID(A1,15,5).

Can you give me the VBA code for this. Suppose if I click a command
button, I want this to happen.

sub grab()
..
..
...
end sub

and then

Private Sub CommandButton1_Click()
call grab
End Sub

Thanks!
-T