Macro to grab "inbetween words" from a text

[Thulasiram]

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

 

[JW]

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]

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)

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

 

[Thulasiram]

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)

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!

 

[JW]

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

 

[JW]

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]

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

 

[JW]

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]

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

 

[JW]

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]

Works great!

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: