Adding Period after Any Initial

[Ed Wong]

Hi all,

I know how to add a period to the middle initial, but I can't figure out how to add a period if there is a first or last initial.

Example:

John Albert Smith = John Albert Smith

John A Smith = John A. Smith

J A Smith = J. A. Smith

J Albert Smith = J. Albert Smith

John Albert S = John Albert S.

In Excel, I've been using this to add a period to a middle initial assuming the whole name is in cell B2:

=IF(ISNUMBER(SEARCH(" ? ",B2)),LEFT(B2,SEARCH(" ? ",B2)+1)&"."&RIGHT(B2,LEN(B2)-SEARCH(" ? ",B2)-1),B2)

Just recently, my client has started to have a first and last initial in Excel. I have not been able to expand this formula to find first and last initial.

I'm currently breaking the whole name with text to column, using the length function in the first column to add a peoriod. Then stitch the name back again.

So I have a working "grind it out" way. But it would be great to be able to do this with one formula. If it can't be done, so be it.

Thank you so much in advance,

Ed

[007design]

if you use a javascript function rather than qlingo it's trivial

function periodize(inputString){

var result = "";

var bits = inputString.split(' ');

for (var b in bits){

if (bits[b].length==1)

result += bits[b]+'.';

else

result += bits[b];

if (b < bits.length-1)

result += ' ';

}

return result;
}

never let anyone tell you something "can't be done".  there is ALWAYS a way.

[Ed Wong]

Thank you 007,

Your always there for me lately.

Now if someone could take a look at this from an Excel or QLingo function, I'd be much appreciated.

Thanks again,

Ed

[Ed Wong]

[Ed Wong]

Okay folks, here is an Excel macro that my partner Ricky Poe found. It works on any initial when the complete name is in one data column.

Change the "B" to whatever column you intend to run this on:

Sub Macro()

lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

For lngRow = 1 To lngLastRow

arrData = Split(Range("B" & lngRow), " ")

For intTemp = 0 To UBound(arrData)

If Len(arrData(intTemp)) = 1 Then

arrData(intTemp) = arrData(intTemp) & "."

End If

Next

Range("B" & lngRow) = Join(arrData, " ")

Next

End Sub

On Wednesday, March 13, 2013 10:20:38 AM UTC-5, Ed Wong wrote: