computing
  • 0

Solved Text To Columns Based On Uppercase

  • 0

If I have data that needs to be separated into columns that looks like this:
JaneDoe
MaryannSmith
RobertWhite

Is there a way to separate into a first name and last name column?

Thanks – Alice

Share

1 Answer

  1. A shorter version is this Array Formula placed in B1 to extract the first name from A1:

    =LEFT(A1,SMALL(FIND(CHAR(ROW(INDIRECT(“65:90″))),A1&”ABCDEFGHIJKLMNOPQRSTUVWXYZ”),2)-1)

    Confirmed with Ctrl-Shift-Enter

    Then this non-array formula in C1 for the last name:

    =REPLACE(A1,1,LEN(B1),””)

    Credit goes to:

    http://www.ozgrid.com/forum/showthr…

    Things get much,much more complicated if you have names like:

    MaryJoMcGillicutty
    BillO’Reilly
    RobertGriffenIII

    Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

    • 0