Extracting Initials

[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

 

[Jeff Boyce]

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.

 

[John Spencer (MVP)]

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

 

[Mo]

Thanks very John. Did the job perfectly with one (very minor) alteration!