Extracting Initials

  • Thread starter Thread starter Mo
  • Start date Start date
M

Mo

The problem is how to extract initials from firstnames that look like 'BETTY
ANNE MARY'.

I need the data in two fields so that it looks like this: 'BETTY' 'A M'.

Now it's fairly easy to get 'BETTY' with the following:
'IIf(InStr([forenames],"
")=0,[forenames],Left([forenames],InStr([forenames]," ")-1))'.

It's also not too much of a problem to get the 'A' from 'ANNE' if I do the
following: 'IIf(InStr([forenames]," "),Mid([forenames],InStr([forenames],"
")+1),"")'. Then I can use Left([middlename], 1) to give me 'A'.

I'm just stumped on the last part - how to get the 'A M'.

Can anyone help?

TIA
 
Conceptually, if you take that last string, from which you derive the "A.",
and look to the right of the space following "Anne", you'll get the final
word. The potential problem I see is with strings that don't have two extra
firstnames.
 
I would think that a User Defined Function would be required. Something like
the following UNTESTED function may work.

Public function fInitialLetters(StrIn)
Dim varArray as Variant
Dim iLoop as Integer

varArray = Split(strIn," ")

For iLoop = lBound(varArray) + 1 to UBound(varArray)
fInitialLetters = fInitialLetters & Left(varArray(iLoop),1)
Next iLoop

End Function
 
Back
Top