About Us

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to copy the first word or two words from a cell containing a complete sentence to another cell	jonny	Excel Discussion (Misc queries)	7	May 19th 23 03:43 AM
How do I abbreviate words?	shangud	Excel Discussion (Misc queries)	2	August 17th 08 12:50 PM
Delete words in cell if that word is in red	kaiser	Excel Programming	3	October 17th 05 07:51 AM
Separate last word in cell with more than 2 words?	Pat Mayton	Excel Worksheet Functions	2	March 24th 05 03:37 PM
Comparing Two Columns - Multiple Words in Same Same Word	Blah[_2_]	Excel Programming	3	July 20th 03 06:49 PM

Menu

About Us