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.

 

[lacogada]

@Rick
@Barry

I've tried both of your formulas, and it does not place a period after
the last letter ..... is this correct ?

 

[barry houdini]

Sorry, didn't realise you wanted that.....try changing to

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

 

[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

For those who find variations interesting, here is another way to write this UDF...

Function InsertDots(ByVal S As String) As String
  InsertDots = Format(S, Replace(String(Len(S) + (Len(S) > 0), "X"), "X", "@.") & "@")
End Function

 

[lacogada]

Thank You

 

[Rick Rothstein]

@Rick
@Barry

I've tried both of your formulas, and it does not place a period after
the last letter ..... is this correct ?

I didn't realize you wanted that either. Given that, I would use this modification of the second UDF that I posted as it is nice and compact...

Function InsertDots(ByVal S As String) As String
  InsertDots = Format(S, Replace(String(Len(S), "X"), "X", "@."))
End Function

 

[Rick Rothstein]

I didn't realize you wanted that either. Given that, I would use this modification of the second UDF that I posted as it is nice and compact...

Function InsertDots(ByVal S As String) As String
  InsertDots = Format(S, Replace(String(Len(S), "X"), "X", "@."))
End Function

But just in case you wanted to see the modification to my first posted UDF, this is what it would look like...

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

 

[Boertjie321]

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.

Hello sir. This formula works great. The only challenge is that it doesn't place a period after the last initial. When I try and add a period after the last (or only) initial, it makes two periods.

 

[Peter_SSs]

Hello sir. This formula works great. The only challenge is that it doesn't place a period after the last initial.

Welcome to the MrExcel board!
The code you referred to is in post #7. Did you read the rest of the thread? In particular, did you read post #15 or #16?