TRIM IF CELL CONTAINS ONE WORD, ABBREVIATE IF IT CONTAINS MULTIPLE WORDS

[l.voisin]

Hello,

I wish to combine two cells by using cell A1 and trimming or abbreviating cell B1.
What function and/or macro can I use to recognize whether the cell B1 contains one or mutliple words, in which case it would trim to 3 first letters or abbreviate to first letter of each word only, respectively. So far I have managed to do:

=A1 &concatenate(arrayFormula(if(not(isError(regexextract(split(B1," "),"\w+"))),left(split(B1," "),3)," " & split(B1," "))))

But thid will take the first three letters of each word...

Please help

 

[nosliwgerg2]

Paste the below function to a standard code module. Then enter "=ConcatAndAbbrev(A1, B1)" in the desired cell to concatenate and abbreviate the contents of cells A1 and B1.

Function ConcatAndAbbrev(text1 As String, text2 As String) As String
Dim arr As Variant
Dim str As String
Dim i As Integer, k As Integer

text1 = Trim(text1): text2 = Trim(text2)
str = text1 & " "
k = Len(text2)
If k > 3 Then k = 3

arr = Split(text2, " ")
If UBound(arr) = 0 Then
ConcatAndAbbrev = str & Left$(text2, k)
Else
For i = LBound(arr) To UBound(arr)
'exclue UCase if don't want conversion to upper case
arr(i) = UCase(Trim(arr(i)))
'exclude zero length elements caused by double spaces
'not necessary if not adding "." because concatenating "" has no effect
If Len(arr(i)) > 0 Then str = str & Left$(arr(i), 1) & "."
Next
ConcatAndAbbrev = str
End If
End Function

Greg Wilson