How to place periods between initials

[sue_ellen_27]

Hi,

How can I place periods between initials if I have for example ABC in cell A1 and want to change it to A.B.C.?

Thanks.

 

[jim may]

Excel Workbook
	A	B	C
1	ABC	A.B.C	<< If Only 3 Letters in A1
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=LEFT(A1) & "." & MID(A1,2,1) & "." & RIGHT(A1)

 

[sue_ellen_27]

Thanks four your reply! It works!

The only thing is that the amount of initials varies. It can be one, two, three or for. How can I place this into the formula?

 

[fcorn]

or...

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

if it can be 1, 2, or 3 characters in length.

 

[sue_ellen_27]

Many thanks!

One last question, the initial go up to four letters. Which part of the formula do I have to change?

 

[barry houdini]

Try this version for any number up to 4

=SUBSTITUTE(TRIM(MID(A1,1,1)&" "&MID(A1,2,1)&" "&MID(A1,3,1)&" "&MID(A1,4,1))," ",".")

 

[Rick Rothstein]

If, in the future, you could possibly be faced with longer text to insert dots into, then you might want to consider this UDF (user defined function)... it adapts automatically to whatever the length of the text is.

Function InsertDots(ByVal S As String) As String
  If Len(S) Then InsertDots = Left(Replace(StrConv(S, vbUnicode), Chr(0), "."), 2 * Len(S) - 1)
End Function

If you have never worked with UDFs before, they are quite easy to use. You install the code in the same place that macros get install to... a standard module. If this is new to you... press ALT+F11 from any worksheet to go into the VB editor, then click Insert/Module once there and copy/paste the code into the code window that opened up). Once you have installed the code, go back to the worksheet and use the function just like you would any other Excel function. For this case, you would simply put this into your cell...

=InsertDots(A1)

This formula can be copied down or across as needed.

 

[mwinte10]

An expansion of fcorn's formula for four characters is this...

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

 

[barry houdini]

....one more way....

=SUBSTITUTE(TRIM(REPLACE(REPLACE(REPLACE(A1,2,0," "),4,0," "),6,0," "))," ",".")

 

[Rick Rothstein]

*** Ignore ***