How to get full name from full text in VBA excel?

Question

Visual Basic

there is a column with full name (Ivanov Ivan Ivanovich), how to make an abbreviation to Ivanov I.I. ?
Column one, can not be divided

Answer 1

Using regular expressions (you need to connect the library to the project for it to work):
Tools – References…
☑ Microsoft VBScript Regular Expression
Code:

Public Function getShortName(strText As Variant) As String
    Dim myRegExp As New RegExp
    Dim matches As MatchCollection

    myRegExp.Global = True
    myRegExp.IgnoreCase = True
    myRegExp.Pattern = "(\S+)\s+(\S)\S*\s+(\S)\S*"
    Set matches = myRegExp.Execute(strText)
    
    getShortName = matches(0).SubMatches(0) & " " & matches(0).SubMatches(1) & "." & matches(0).SubMatches(2) & "."
End Function

It is possible through Split (), even shorter code will turn out, although it will stumble on double spaces.

Function getShortName(strText As String) As String
    Dim a
    a = Split(strText, " ")    
    getShortName = a(0) & " " & Left(a(1), 1) & "." & Left(a(2), 1) & "."
End Function

And you can generally make a formula - start from the search for text (spaces)