Tag Archives | spaces

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

BillyJoSpears

and in cell B1 we typed

=InsertSpaces(A1)

you would see in B1 the text

Billy Jo Spears

 

Finding the Last Space Character in an Excel cell

I am often asked in my Excel Advanced User course if it is possible to extract just a person’s last name from a cell containing their full name.

Yes it is but it is not as simple as using the RIGHT function. As people can have middle names or initials the cell could contain more than one space character to deal with. Moreover, the text might contain leading or trailing spaces that need to be removed so the specific last space between names can be found.  This is done using the TRIM function.

The formula shown below removes leading and trailing spaces then, using the REPT function, substitutes for each space found repeating spaces for the number of characters in the trimmed text. With the now very expanded text that has lots of spaces in blocks within it, we use the RIGHT function to extract the text from the expanded string for the length of the original trimmed string will give the last name with a load of leading spaces. Finally the last TRIM function removes any leading spaces, leaving the last name intact. This formula also handles having leading and trailing spaces and also circumstance where there is no space in the cell. The formula assumes the full name is in cell A1. Please adjust the cell references to fit your circumstances.

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), 
	LEN(TRIM(A1))))

(All the above is typed on one line!)