Simple but frustrating

[Guest]

Hi...

I'm looking for code that will convert a column of first and surnames to an
adjacant column of initials. eg. column A says Bill Smith, I want column B to
say BS. This should apply to any name of any length...

Thanks in advance...

Gordon.

 

[Guest]

This is a fomulma "B2", which refers to cell "A2". It can be copied down your
column. Fope this will do the job. If it has to be in code, then edit the
formula with the recoder turned on.
=LEFT(A2,1)&" "&MID(A2,FIND(" ",A2,1),LEN(A2)-FIND(" ",A2,1)+1)

 

[Mike Fogleman]

Here is a cell formula that will do that:
=CONCATENATE(LEFT(A1,1),MID(A1,FIND(" ",A1,1)+1,1))

Mike F

 

[KL]

....and even shorter:

=LEFT(A1,1) & MID(A1,FIND(" ",A1)+1,1)

KL

 

[Lonnie M.]

Hi, just incase you are trying to do this in VBA you may want to try
the following--'Find' doesn't get it done in the VBA editor(at least
not mine--Office 2003). The above examples work great as a worksheet
formulas.

Option Explicit
Sub conc_names()
Dim myVariable$, myCellValue$
myCellValue = Cells(1, 1)
myVariable = Left(myCellValue, 1) & Mid(myCellValue, InStr(1,
myCellValue, " ", 1) + 1, 1)
End Sub

HTH--Lonnie