Extract 4 characters of each word in a multiple word field

[blurworld]

Hi

How do i ....
Extract the first 4 characters of each word in a multiple word field i.e.

If i had a name DAVID JONES
I want it to become.... DAVIJONE
or FOX TELEVISION would become FOXTELE

Thanx for your help!

-Martin

 

[mp9]

This may work - I haven't tested it though. The function takes your original string and returns a modified version:

Function fctnTakeFour(strText As String) As String
'First make sure that all mulitple spaces are reduced to one space
Do Until InStr(1, strText, "  ", vbTextCompare) = 0
    strText = Replace(strText, "  ", " ", , , vbTextCompare)
Loop
'Then take the first four chars of each word.
Dim lngSpace As Long
Do Until InStr(1, strText, " ", vbTextCompare) = 0
    lngSpace = InStr(1, strText, " ", vbTextCompare)
    If lngSpace > 4 Then
        fctnTakeFour = fctnTakeFour & Left$(strText, 4)
    Else
        fctnTakeFour = fctnTakeFour & Left$(strText, lngSpace - 1)
    End If
    strText = Mid$(strText, lngSpace + 1)
Loop
'Finally allow for leftover text after the last space.
If Len(strText) > 0 Then fctnTakeFour = fctnTakeFour & Left$(strText, 4)
End Function