need to change AAAA into A.A.A.A.

  • Thread starter Thread starter vreedejunior
  • Start date Start date
V

vreedejunior

hello,

my list is 1 column and approximately 500 rows long. the name code
used are combinations of 1 to 4 letters, and they are noted as follows
for example AAAA. they are supposed to be noted as follows: A.A.A.A.
thus separated by points. is it possible to adjust the complete lis
all at once?? i've tried several things, but helas....

THANX!!!!!!
 
vreedejunior > said:
hello,

my list is 1 column and approximately 500 rows long. the name codes
used are combinations of 1 to 4 letters, and they are noted as follows:
for example AAAA. they are supposed to be noted as follows: A.A.A.A.,
thus separated by points. is it possible to adjust the complete list
all at once?? i've tried several things, but helas....
Click to expand...

There may be a more elegant way, but this should work. Suppose the list is
in column A starting in A1. Use this formula in B1, and copy down as far as
required:
=LEFT(A1,1)&"."&IF(LEN(A1)>1,MID(A1,2,1)&".","")&IF(LEN(A1)>2,MID(A1,3,1)&".
","")&IF(LEN(A1)>3,RIGHT(A1,1)&".","")
You can then Copy column B and Paste Special > Values over column A, finally
deleting column B.
 
hello,

my list is 1 column and approximately 500 rows long. the name codes
used are combinations of 1 to 4 letters, and they are noted as follows:
for example AAAA. they are supposed to be noted as follows: A.A.A.A.,
thus separated by points. is it possible to adjust the complete list
all at once?? i've tried several things, but helas....

THANX!!!!!!!
Click to expand...

One way is to use a VBA Macro:

To enter this, <alt><F11> opens the visual basic editor.
Ensure your project is highlighted in the Project Explorer, then Insert/Module
and paste the code below into the window that opens.

To use this, select the range you wish to change, then <alt><F8> and select the
InsertPoints macro.

======================================
Sub InsertPoints()
Dim c As Range
Dim Str As String, NextLtr As String
Dim Cnt As Integer
Const Point As String = "."

For Each c In Selection
Str = ""
For Cnt = 1 To Len(c.Text)
NextLtr = Mid(c.Text, Cnt, 1)
If NextLtr <> Point Then Str = Str & NextLtr & Point
Next Cnt
c.Value = Str
Next c

End Sub
=======================================


--ron
 
Back
Top