Split text in excel with this simple excel formula.

If you have a string of text, like a full name without spaces, and you want to split it into 2 columns: one for the first name and another one for the last name, you look for a pattern, like the change in letter case…

Excel gives us different options to perform the same task.

In this article I show you how to split text at the Second Upper Case character.

I’ll be using 3 methods, I call them Hard, Easy and Effortless…

Along the way I’ll show you some useful Excel tips and tricks

Split Text in Excel

You can download the exercise file and follow along by clicking on the button

In this worksheet we have a list of Full names in column A, but it has no spaces and it appears as on string of text.

There is an identifiable pattern where the Last name in the entire list starts with a capital letter, and because the First name as well starts with a capital letter, then if we are able to locate the position of the Second Upper Case letter, we’ll be breaking just before it.

Method # 1: The Hard method – Using Functions

Locating the position of the Second Upper case letter:

I’ll be using a combination of Functions to do that

• ROW: Returns the Row umber
  I will use it to provide a number between 65 and 90 to the CHAR function.
• CHAR: returns a character based upon a number you specify.
  =CHAR(65) ►A , while =CHAR(90) ► Z both upper case. By providing numbers between 65 and 90 you get all the letters in Upper case.
• FIND: returns the position of a Character within a string of text. It is case sensitive

I’ll be combining the 3 functions in cell B2 then use the F9 key to chsck what each part is returning:

= FIND(CHAR(ROW($65:$90)),A2,2)

Let’s test this function by hitting F2 (Edit Mode):

Select the ROW function only then hit F9 (Calculate Now) ► It returns an array of numbers between 65 and 90 in curly brackets.

Now let’s Undo CTRL + Z

Select the CHAR function with the nested ROW function and hit F9 ► It returns the letters from A to Z all in Upper Case.

Now let’s Undo CTRL + Z

Finally let’s select the entire Find Function with the nested CHAR and ROW functions, then Hit F9 ► it checks Cell A2 for each one of the Upper Case letter (A to Z) in the second occurrence (Last argument 2) ► when it locates the second Upper Case Character, it returns the position of that character in cell A2. While for All other characters it returns a Value error.

Because the first Full Name is “BardelliGreta”, letter “G” was found as the second Upper Case at position 9

Now let’s Undo CTRL + Z

To get rid of all Value errors, I will wrap the Find function into an IFERROR function. The value if error will be nothing and we write it: “”

= IFERROR(FIND(CHAR(ROW($65:$90)),A2,2),””)

All the value errors have been converted to “” and we only see the single number corresponding the second Upper Case character. Note the curly brackets which mean it’s an array of values.

Now let’s Undo CTRL + Z

Finally we need to get the only number we have in this array, so, we’ll wrap the bunch of functions in a MIN function.

= MIN(IFERROR(FIND(CHAR(ROW($65:$90)),A2,2),””))

If you hit Enter you get a #Value! Error because it’s an array Function that requires hitting CTRL + SHIFT + ENTER

Now we can copy the functions down by double clicking on the autofill handle in the lower right corner.

Because our goal is to extract the First name in one column and the Last name in another column, I create a function in cell C2. I use a LEFT function since the First name is to the left side of A2. I also specify the number of characters to extract by referring to the number returned in column B minus one (since I want 1 character before the second Upper case letter)

In Cell C2 type:

=LEFT(A2,B2-1)

The First name is extracted ► You can copy the function ll the way down.

Finally for the Last name, I’ll be using a REPLACE function in cell D2 which requires four arguments. I’ll be replacing the contents of cell A2, from the first character 1 up to (not including) the Second Upper Case letter (in cell B2) and I’ll put nothing “” instead.

=REPLACE(A2,1,B2-1,””)

That returns the Last Name

You can then copy the function all the way down.

That was a lot of Functions. That’s why I called it the Hard method.

Method # 2: The Easy method – Using Power Query

Let’s activate the next worksheet where we have the same exact list of names.

Select any single cell in the list, then hit CTRL + T followed by hitting Enter to convert the list into a table. Let’s name the table (Design Tab) “MyNames”.

To send the Table to Power Query, click on the Data Tab of the Ribbon and select From Table.

The Query Editor opens on top of Excel.

To split the Full Name column, on the Home Tab click on Split Column ► and from the menu select ► lowercase to Uppercase ► Now you have 2 columns

Rename the Columns, First and Last.

To send the data back to Excel, Click on Close & Load to the Left side of the Home Tab ► Select Close & Load To ► Existing Worksheet ► collapse and select Cell D1 ►OK ► Load

Now we’re done the data is now loaded in Excel. We split the Full name into First and Last columns. If more names are added to the table in Column A, all what you need to do is to Refresh the Query.

That was the Easy method.

Method # 3: The Effortless method – Using Flash Fill

Let’s go to the Last sheet, where we have the same exact list of Names.

To extract the First name I select Cell B2 and I’ll be using the Flash Fill Technique. The Flash Fill means you type the first entry as a model or a pattern, then you ask Excel to simulate that pattern for all remaining cells.

In Cell B2 type: Bardelli (the first name from Cell A2) ► then hit Enter

Now to use Flash Fill You can either:

Go to the Data Tab and click Flash Fill

Type the first character of the second record in B3 ► Excel shows all First Names in light grey color ► Hit Enter

Or simply use the shortcut CTRL + E

Repeat for the Last Name in Cell B2, type: Greta ► Enter ► CTRL+E

That was the effortless method.

Notes:

Although the first method using Functions was the most difficult to create, yet in case of any changes in the source list the result instantly updates.

On the Other hand, the last method using Flash Fill was the Fastest and easiest but it does not update in case of any changes in the source list.

Let me know in a comment which of the three methods you prefer.

Microsoft also has a simple article explaining to you how to split text in excel. Click here to learn more.

You can watch the Video Tutorial here