Split cells based on Capital Letters

**AntyLani** · 05-17-2013, 10:28 AM

I have data that downloads as:

JohnSmith M
JaneDoe L
RobertJones A

I need to split the first and last name into two separate columns. Can anyone help with an Excel formula. I don't know VBA.

Thanks.

**nathansav** · 05-17-2013, 10:32 AM

Splitting on Space

First name

=MID(I17,1,FIND(" ",I17,1))

Last name

=MID(I17,FIND(" ",I17,1)+1,LEN(I17)-FIND(" ",I17,1)+1)

**Kevin UK** · 05-17-2013, 11:24 AM

Hi AntyLani

Asssuming your data is in A1:A3, try the following.

In B1: =LEFT(A1,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1) This is a array formula, CTRL + SHIFT + ENTER to commit and copy down.
In C1: =MID(A1,LEN(B1)+1,LEN(A1)-LEN(B1)-LEN(D1)) and copy down.
In D1: =RIGHT(A1,LEN(A1)-FIND(" ",A1)) and copy down.

Split cells based on Capital Letters

LinkBack

Thread Tools

Rate This Thread

Display

Split cells based on Capital Letters

Re: Split cells based on Capital Letters

Re: Split cells based on Capital Letters

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions