Capture first letters of string of words in Excel or Word

Thanks for the reply Martin. I'm really hoping there is a simpler way to accomplish this using Excel formulas rather than code. Is that possible?

If not, can you tell me where to paste the code and in what type of VBA screen? Also, will this work for multiple rows?

I'm sorry, I do not know what WTYWAT means. It brings to mind WYSIWYG. Is it similar?

Thanks

In Excel, Press Alt+F11 to open the Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane. If you don’t see an existing module then select Insert -> Module from the menu bar. Otherwise just select the module.

Copy the UDF (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor "Module1" window

Press Alt+F11 again to go back to Excel

When you close the workbook you will need to save it as an xlsm, xlsb or xls file if it’s not already one of those.

WTYWAT = We Thought You Was A Toad.

I believe Mr. Liss picked the first letters of your screen name to get WTYWAT, after making the substitution of You for U.

I did some research and could not find any flexible (meaning more then a set, few number of words) formula that does what you want.

The name of my function can be renamed anything you like by changing all 3 occurrences of the name.

Brad,

Thanks for the comments and sample workbook. Unfortunately it gave me a #NAME? error in H2:I4. I suspect that is due to me still using Excel 2010. I have continued to be reluctant to give up the option of having multiple worksheets share a common toolbar in a single workbook.

Thanks

Where does a discussion of toolbars come into this?

If you put my UDF in your Personal.xlsb file I believe it will be available to all worksheets and workbooks.

With Excel 2016 and earlier, you do not have the ability to concatenate an arbitrary number of strings without using VBA. I suggest you use Mr. Liss' UDF.

If you must avoid VBA, you can use Text to Columns to split your source text into individual words like you had suggested in your original question body. And then in a row underneath, copy across a formula in cell B3 (assuming that contains your first word) that produces a growing concatenation, The last cell with this formula carries your result.

=A3 & LEFT(B2,1)

Select allOpen in new window

Brad

Excel 2013 introduced the Single Document Interface, replacing the Multiple Document Interface used hitherto. With SDI, each workbook gets its own toolbar. WeThotUWasAToad finds this behavior inconvenient, and continues to use Excel 2010 where one toolbar serves all open workbooks.

Re: WTYWAT

Of course! LOL

In case you don't recognize the origin of that phrase, it's from the movie, O Brother Where Art Thou. I think it's the funniest line I've ever heard in a movie. :)

How about FILTERXML?

=TEXTJOIN("", TRUE,LEFT(TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(A1," ","</y><y>")&"</y></x>","//y"))))

FILTERXML requires Windows Excel 2013 or later (doesn't work on Mac Excel). TEXTJOIN requires Excel 2019 or later. The Asker, however, is using Excel 2010.

Here's a regular expression solution, allowing you to request the first N letters of each word.  The first N letters defaults to 1.

Function Q_29228656(r As Range, Optional letter_count As Long = 1) As String
    Static oRE As Object
    Static lngPriorCount As Long
    Dim oMatches As Object
    Dim oM As Object

    If (oRE is Nothing) Or (letter_count <> lngPriorCount) Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
        oRE.Pattern = "(?:^|\W)(\w{" & letter_count & "})"
        lngPriorCount = letter_count
    End If

    Set oMatches = oRE.Execute(r.value)
    For Each oM in oMatches
        Q_29228656 = Q_29228656 & om.Submatches(0)
    Next

End Function

Select allOpen in new window

Another Regular Expressions-based approach here; if you want to know more about it, please see my article https://www.experts-exchange.com/articles/1336/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

Using the RegExpReplace function from that article, you could use a formula like this:

=RegExpReplace(A1,"( *)(\w)(\w*)","$2",TRUE,FALSE)

Select allOpen in new window

In English, that pattern string means:

• A space, zero or more times, followed by
• A single "word character" (letter, numeral, or underscore), followed by
• Zero or more word characters

The $2 indicates that for each match to the whole pattern string, replace it with the second submatch, which is the single word character (and the first character in each word).

The UDF code:

Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll
    ' controls whether all instances of the matched string are replaced (True) or just the first
    ' instance (False)
    
    ' If you need to replace the Nth match, or a range of matches, then use RegExpReplaceRange
    ' instead
    
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    
    ' If you use this function from Excel, you may substitute range references for all the arguments
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
    
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
    
End Function

Select allOpen in new window

Thanks for the comments and suggestions.