Home/ Questions/Q 1975
Next
Answered
computing
  • 10
Asked: 2021-10-27T08:56:19+00:00 In:

Solved Text To Columns Based On Uppercase

  • 10

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. Best Answer
      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