Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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" wrote: 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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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" wrote: 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" wrote: 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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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" wrote: 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" wrote: 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" wrote: 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! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mid and Find in macro
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 "Erin" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro that does a find and then find next | Excel Programming | |||
Find & Replace and Find & Insert macro help needed | Excel Programming | |||
Get Macro warning, but can't find Macro | Excel Worksheet Functions | |||
change error message when no more for "find" in macro to find | Excel Programming | |||
I need to find a macro to find data cut and paste to another colu. | Excel Programming |