Tag Archives | array

Inserting a Space Character In Text

Following on from my last post about finding space characters, I  was asked how to go about inserting a space character if someone had incorrectly typed in a person’s name and left the space out.

So for example say we had ChrisPage in cell A1 how could you insert the space between the first and last names?  It depends on how many spaces would be needed. In this case as there is only a first and last name so only one space. An array formula within Excel could be used. Here it is:

{=REPLACE(A1, 1+MIN(FIND(CHAR(64+COLUMN($A$1:$Z$1)),MID(A1,2,255)&
(CHAR(64+COLUMN($A$1:$Z$1))))), 0, " ")}

(All the above should be on one line!)

Remember, to use an array formula you must type in the text without the beginning and ending curly brackets, then press Ctrl+Shift+Enter

If, however, the person’s name was something like BillyJoSpears, the array formula would not work. What you could do however, is create a User-Defined Function using VBA. Open up the VB Editor (using ALT+F11) and then from the Menu Bar select Insert, Module. In this module create the following code:

Function InsertSpaces(strName As String)
    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "(\w)(?=[A-Z])"
        InsertSpaces = .Replace(strName, "$1 ")
    End With
End Function

The above VBA function uses the power of VB Script to give  a regular expression that when it finds an upper case character after the first one, it replaces the text before the upper case character with that text again followed by a space. This works for any length of text with any number of upper case characters so would work with multiple names, just First and Last names and so on. To use the function in an excel worksheet just type =InsertSpaces(Range) where Range is the cell reference that has the uncorrected text in it. So if cell A1 held the text


and in cell B1 we typed


you would see in B1 the text

Billy Jo Spears