Fill in a DOT after each character

[Jacco]

How can i fill in a DOT after each character.

I have a list with names, split in two collums, one with the last name and the other with the sure name characters.

Smith AA

Now i like to ad an dot (.) between each character in the secon collum

Result:

Smith A.A.

Who can help me.

 

[mvptomlinson]

If the surname column always has two letters, then this should work:

=LEFT(A1,1)&"."&RIGHT(A1,1)&"."

If it can be one, two or three letters, then:

=IF(LEN(A1)=1,A1&".",IF(LEN(A1)=2,LEFT(A1,1)&"."&RIGHT(A1,1)&".",LEFT(A1,1)&"."&MID(A1,2,1)&"."&RIGHT(A1,1)&"."))

 

[Andrew Fergus]

Hi

The following circular reference formula entered in cell C2 should work for up to 100 characters or thereabouts for the initials in cell B2. To use this you need set the number of iterations to 100 (see menu option Tools > Options > Calculation > Iteration) and press F9 a few times :

=IF(IF(ISERROR(LEN(C2)),0,LEN(C2))
< LEN(B2)*2,MID(B2,LEN(B2)-
IF(ISERROR(LEN(C2)),0,LEN(C2)/2),1)&"."&IF(ISERROR(C2),"",C2),C2)

<LEN(B2)*2 2 p C2) & ?.? LEN(C2)) C2)< , ?? IF(ISERROR(C2) 1) ) 0 IF(ISERROR(LEN(C2)) - MAX(LEN(B2) MID(B2>
Apologies for the layout but the html editor is mucking up my formulas at the moment.

Credit to hady for the concept from here: http://www.mrexcel.com/forum/showthread.php?p=1107631

Andrew

 

[Andrew Fergus]

Please note the formula I provided is not suitable for copying and pasting (hence not very practical) - after copying the formula to a cell it needs to be reconfirmed by pressing the F2 key and then enter. The suggestion provided by mvptomlinson is better if you know the maximum length of the initials. A UDF written in VBA would be better for longer sets of data.

Andrew

P.S. Welcome to MrExcel!

 

[Peter_SSs]

For a maximum of three characters in column B, try this. (Could be expanded a little further if more than 3 is possible).

Excel Workbook
	A	B	C
1	Smith	MM	Smith M.M.
2	Jones	B	Jones B.
3	Black	JGT	Black J.G.T.
Add Dots

 

[Scott Huish]

This UDF should insert a dot after every character in the cell:

Function addDot(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "(.)"
    .Global = True
    addDot = .Replace(r, "$1.")
End With
End Function