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

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(regexextr act(split(B1," "),"\w+"))),left(split(B1," "),3)," " & split(B1," "))))

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

Please help

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

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

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

On Thu, 3 Jul 2014 14:01:36 -0700 (PDT), wrote:

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(regexextr act(split(B1," "),"\w+"))),left(split(B1," "),3)," " & split(B1," "))))

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

Please help

I'm not sure what kind of addins you are using to create that formula.

If you have a regex replace or substitute UDF already, you can use something like:

=A1& REGEX.SUBSTITUTE(B1,"^(\S{1,3})\S*$|(?:\s*(\S)\S*) ","[1][2]")

You may need to replace [1][2] with whatever the symbols are for capturing groups 1 and 2 in the replacement tokens.

If you need to write the entire UDF yourself, then:

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ConcatAbbrev(A1,B1)

in some cell.

===================================
Option Explicit
Function ConcatAbbrev(S As String, S2Abbrev As String) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.Pattern = "^(\S{1,3})\S*$|(?:\s*(\S)\S*)"
ConcatAbbrev = S & .Replace(S2Abbrev, "$1$2")
End With
End Function
=======================================