Mid and Find in macro

[Erin]

Hi!

I'm trying to use this type of formula in a macro, but I can't figure out
the syntax:

Trim(MID(A1,FIND(" ",A1),255))

So if I have "Alpha Dog" in a cell, I want to only show "Dog". The length
of each word or number will vary and there could be multiple spaces between
them.

Appreciate any help!

 

[Mike H]

Erin,

Use VB native instr function which finds the position of a character in a
string

mystring = Trim(Mid(Range("A1"), InStr(Range("A1"), " ")))

Mike

 

[Rick Rothstein]

Your question is not entirely clear to me... are you looking for the 2nd
word in a text string or the last? For example, what did you want to see for
"Alpha Dog Beta"? The reason for my confusion is your use of 255 in the MID
function call (after finding the first space) coupled with your statement
that the number of words can vary.

 

[Mike H]

erin,

I should have added that to do it 'your' way it looks like this

myotherstring = Trim(Mid(Range("A1"), WorksheetFunction.Find(" ",
Range("A1")), 255))

Mike

 

[Erin]

Well actually I meant "number" as in integer -- it could be "1 Dog" instead
of "Alpha Dog", but yes, it could also be "Alpha Dog Beta", in which case I
would want "Dog Beta". I just want to get rid of the first word or numbers
and spaces.

 

[Erin]

I tried both of those but couldn't get either to work. I used "Selection"
instead of "mystring" since I'm using "Range("A1").Select" in the code. I
get the following errors:


Invalid procedure call or argument (for the first string)

Unable to get the Find property of the worksheetFunction class (for the
other string)

What am I missing?

 

[Mike H]

Hi,

So you used

selection = Trim(Mid(Range("A1"), InStr(Range("A1"), " ")))


it would go wrong. If your selecting A1 (and there;s no need to) the syntax
would be

mystring = Trim(Mid(activecell,InStr(activecell," ")))

Mike

 

[Erin]

Lol -- now I've locked my Excel up -- it just sits there with an hourglass.
I'm obviously doing something horribly wrong!

Here's what I have:


Range("A1").Select
Do
Do Until Selection = ""
mystring = Trim(Mid(activecell,InStr(activecell," ")))
Exit Do
Loop
Loop Until Selection = ""

End Sub

 

[Mike H]

Erin,

Your in an endless loop hold down the CTRL key and tao BREAK/Pause and then
click end.

Now lets analyse your code

Range("A1").Select ' Select A1
Do 'Initiate a DO loop
Do Until Selection = "" ' initiate a 2nd do loop with an exit condition of
A1=""
mystring = Trim(Mid(activecell,InStr(activecell," "))) ' Remove first
word
Exit Do 'exit first do
Loop 'never executed
Loop Until Selection = "" ' selection will NEVER = "" so endless loop

End Sub

perhaps you should explain in words what your trying to do!!

Mike



Mike

 

[Erin]

I appreciate your patience because I'm clearly lost.

I have a column with two words in it. I want to remove the first word. Up
until recently, the first word was always five letters so I used this macro
to fix the column:

Range("A1").Select
Do
Do Until Selection = ""
Selection = Trim(Right(Selection, (Len(Selection) - 5)))
Selection.Offset(1, 0).Select
Exit Do
Loop
Loop Until Selection = ""

Now I have six letter words in the column, so I want to modify the macro to
look for the second word and remove anything before it (since I could still
have five letter words also and since I don't know what I'll have in the
future). I tried to build off of my existing macro, but I'm not having any
luck.

The second word could have any amount of characters, and there could be
three or four words also. I just want to remove the first word.

 

[Mike H]

Erin,

This should do what you want

Sub sonic()
Dim MyRange As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
c.Value = Trim(Mid(c.Value, InStr(c.Value, " ")))
Next
End Sub

Mike

 

[Erin]

That works perfectly!

Why would the other code work with the "Right" function and not with "Mid"?
Just curious -- I would think that the code would be similar for both, and
it's completely different.

 

[Mike H]

Hi,

You could make it work with right but MID has a nice quirk in that if you
omit the LEN bit then it will return all of the string that left.

If I was using RIGHT then perhaps I would use INSTRREV

look that up in VB help and your learning curve becomes a bit steeper.

I'm glad we got ther and thanks for the feedback.

Mike