Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-word text
My cells have content like this:
..VAVXB ..CQQAP ..WQQLK I would like to, in an automated way, change them (either individually or by highlighting the range) to: VAVXB.X CQQAP.X WQQLK.X In other words, delete the leading . and add .X at the end. I'm not too good at macros, but I tried recording the keystrokes in one of the cells, but re-playing the macro in another cell did not work. Appreciate your assistance. If a macro, the full text which I can cut and paste verbatim will be ideal. Also, if a macro, I'm not sure how to enter and save it under All Personal Workbooks, so it is available to all my workbooks. How is this done? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-word text
Hi,
Alt+F11 to open VB editor. right click the 'Modules' in Personal.xls and insert module. Double click the newly insertede module and paste the code in that. Select the data and run it. Sub servient() For Each c In Selection c.Value = Trim(Mid(c.Value, 2, Len(c.Value))) & ".x" Next End Sub On closing Excel be sure to save Personal.xls when prompted Mike "Tenacity9" wrote: My cells have content like this: .VAVXB .CQQAP .WQQLK I would like to, in an automated way, change them (either individually or by highlighting the range) to: VAVXB.X CQQAP.X WQQLK.X In other words, delete the leading . and add .X at the end. I'm not too good at macros, but I tried recording the keystrokes in one of the cells, but re-playing the macro in another cell did not work. Appreciate your assistance. If a macro, the full text which I can cut and paste verbatim will be ideal. Also, if a macro, I'm not sure how to enter and save it under All Personal Workbooks, so it is available to all my workbooks. How is this done? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-word text
Looking at your Mid function call in isolation...
Mid(c.Value, 2, Len(c.Value)) Theoretically, your 3rd argument should be Len(c.Value)-1; however, Len(c.Value) works because any value greater than the number of characters remaining in the text will return only the remaining characters. So, this would have worked just as well... Mid(c.Value, 2, 100000) However, the reason I'm posting this message is to point out for those reading this thread that the 3rd argument is optional and, when omitted, it automatically returns the remainder of the characters in the text. So, your Mid function call could have been this as well... Mid(c.Value, 2) meaning this active code statement would have also worked inside your loop... c.Value = Trim(Mid(c.Value, 2)) & ".x" -- Rick (MVP - Excel) "Mike H" wrote in message ... Hi, Alt+F11 to open VB editor. right click the 'Modules' in Personal.xls and insert module. Double click the newly insertede module and paste the code in that. Select the data and run it. Sub servient() For Each c In Selection c.Value = Trim(Mid(c.Value, 2, Len(c.Value))) & ".x" Next End Sub On closing Excel be sure to save Personal.xls when prompted Mike "Tenacity9" wrote: My cells have content like this: .VAVXB .CQQAP .WQQLK I would like to, in an automated way, change them (either individually or by highlighting the range) to: VAVXB.X CQQAP.X WQQLK.X In other words, delete the leading . and add .X at the end. I'm not too good at macros, but I tried recording the keystrokes in one of the cells, but re-playing the macro in another cell did not work. Appreciate your assistance. If a macro, the full text which I can cut and paste verbatim will be ideal. Also, if a macro, I'm not sure how to enter and save it under All Personal Workbooks, so it is available to all my workbooks. How is this done? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-word text
Thanks Rick,
I've always used Len(.. to ensure I got all of the text when doing this type of thing and never bothered with the -1 because it's superfluous but had never recognised that you could omit this argument altogether and still get all the text. I know for future :) Mike "Rick Rothstein" wrote: Looking at your Mid function call in isolation... Mid(c.Value, 2, Len(c.Value)) Theoretically, your 3rd argument should be Len(c.Value)-1; however, Len(c.Value) works because any value greater than the number of characters remaining in the text will return only the remaining characters. So, this would have worked just as well... Mid(c.Value, 2, 100000) However, the reason I'm posting this message is to point out for those reading this thread that the 3rd argument is optional and, when omitted, it automatically returns the remainder of the characters in the text. So, your Mid function call could have been this as well... Mid(c.Value, 2) meaning this active code statement would have also worked inside your loop... c.Value = Trim(Mid(c.Value, 2)) & ".x" -- Rick (MVP - Excel) "Mike H" wrote in message ... Hi, Alt+F11 to open VB editor. right click the 'Modules' in Personal.xls and insert module. Double click the newly insertede module and paste the code in that. Select the data and run it. Sub servient() For Each c In Selection c.Value = Trim(Mid(c.Value, 2, Len(c.Value))) & ".x" Next End Sub On closing Excel be sure to save Personal.xls when prompted Mike "Tenacity9" wrote: My cells have content like this: .VAVXB .CQQAP .WQQLK I would like to, in an automated way, change them (either individually or by highlighting the range) to: VAVXB.X CQQAP.X WQQLK.X In other words, delete the leading . and add .X at the end. I'm not too good at macros, but I tried recording the keystrokes in one of the cells, but re-playing the macro in another cell did not work. Appreciate your assistance. If a macro, the full text which I can cut and paste verbatim will be ideal. Also, if a macro, I'm not sure how to enter and save it under All Personal Workbooks, so it is available to all my workbooks. How is this done? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-word text
Omitting the 3rd argument (when looking for the remainder of the text) is a
nice short cut and it saves a function call (although that function call is an extremely fast one in VB). What is really a shame (in my view) is that in the worksheet formula's MID function, the 3rd argument is not optional (although it does permit one to specify more than the number of remaining characters in the text). -- Rick (MVP - Excel) "Mike H" wrote in message ... Thanks Rick, I've always used Len(.. to ensure I got all of the text when doing this type of thing and never bothered with the -1 because it's superfluous but had never recognised that you could omit this argument altogether and still get all the text. I know for future :) Mike "Rick Rothstein" wrote: Looking at your Mid function call in isolation... Mid(c.Value, 2, Len(c.Value)) Theoretically, your 3rd argument should be Len(c.Value)-1; however, Len(c.Value) works because any value greater than the number of characters remaining in the text will return only the remaining characters. So, this would have worked just as well... Mid(c.Value, 2, 100000) However, the reason I'm posting this message is to point out for those reading this thread that the 3rd argument is optional and, when omitted, it automatically returns the remainder of the characters in the text. So, your Mid function call could have been this as well... Mid(c.Value, 2) meaning this active code statement would have also worked inside your loop... c.Value = Trim(Mid(c.Value, 2)) & ".x" -- Rick (MVP - Excel) "Mike H" wrote in message ... Hi, Alt+F11 to open VB editor. right click the 'Modules' in Personal.xls and insert module. Double click the newly insertede module and paste the code in that. Select the data and run it. Sub servient() For Each c In Selection c.Value = Trim(Mid(c.Value, 2, Len(c.Value))) & ".x" Next End Sub On closing Excel be sure to save Personal.xls when prompted Mike "Tenacity9" wrote: My cells have content like this: .VAVXB .CQQAP .WQQLK I would like to, in an automated way, change them (either individually or by highlighting the range) to: VAVXB.X CQQAP.X WQQLK.X In other words, delete the leading . and add .X at the end. I'm not too good at macros, but I tried recording the keystrokes in one of the cells, but re-playing the macro in another cell did not work. Appreciate your assistance. If a macro, the full text which I can cut and paste verbatim will be ideal. Also, if a macro, I'm not sure how to enter and save it under All Personal Workbooks, so it is available to all my workbooks. How is this done? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a text word or text string to a number | Excel Discussion (Misc queries) | |||
losing text from text box when pasting selection to word | Excel Discussion (Misc queries) | |||
Matching CELL text (full word) to full word in string in another | Excel Programming | |||
I want to link, not just copy,Word source text to a text box in Ex | Excel Worksheet Functions | |||
Text not copying as text from Word to Excel 2003 | Excel Discussion (Misc queries) |