Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple but frustrating
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple but frustrating
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) "Gordon" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple but frustrating
Here is a cell formula that will do that:
=CONCATENATE(LEFT(A1,1),MID(A1,FIND(" ",A1,1)+1,1)) Mike F "Gordon" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple but frustrating
....and even shorter:
=LEFT(A1,1) & MID(A1,FIND(" ",A1)+1,1) KL "Gordon" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple but frustrating
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Frustrating SUMPRODUCT problem. | Excel Discussion (Misc queries) | |||
IRR, XIRR and NPV - a very frustrating problem | Excel Discussion (Misc queries) | |||
Very Frustrating - Please Help! | Excel Worksheet Functions | |||
Frustrating Problem | New Users to Excel | |||
Bizarre and frustrating bug | Excel Programming |