Re-word text

[Tenacity9]

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.

 

[Mike H]

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

 

[Rick Rothstein]

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"

 

[Mike H]

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]

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).