ExcelBanter - View Single Post - TRIM IF CELL CONTAINS ONE WORD, ABBREVIATE IF IT CONTAINS MULTIPLE WORDS

Ron Rosenfeld[_2_]

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
=======================================