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!

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

"Erin" wrote:

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!

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

"Mike H" wrote:

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

"Erin" wrote:

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!

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" wrote:

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

"Mike H" wrote:

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

"Erin" wrote:

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!

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" wrote:

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" wrote:

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

"Mike H" wrote:

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

"Erin" wrote:

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!

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" wrote:

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" wrote:

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" wrote:

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

"Mike H" wrote:

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

"Erin" wrote:

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!

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.

--
Rick (MVP - Excel)


"Erin" wrote in message
...
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!

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.

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Erin" wrote in message
...
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!