Split names with no space:

[clemenger]

GrahamMaurice - how to obtain two columns = Maurice and the other = Surname = Graham?

Thinking this is simple excel; but I am simple!; best Tony Clemenger.

[pike]

Hi Clemenger
A2=GrahamMaurice
B2=MID(A2,SMALL(INDEX(FIND(CHAR(64+ROW($1:$26)),A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),0),2),LEN(A2))
C2=LEFT(A2,LEN(A2)-LEN(B2))

[snb]

Array formula:

=LEFT(A1;MAX((N(CODE(MID(A1;ROW(OFFSET(A1;;;LEN(A1);1));1))<91)*ROW(OFFSET(A1;;;LEN(A1);1))))-1)&" " &MID(A1;MAX((N(CODE(MID(A1;ROW(OFFSET(A1;;;LEN(A1);1));1))<91)*ROW(OFFSET(A1;;;LEN(A1);1))));LEN(A1))

[clemenger]

Excel tells me to put an ' prior to the =
These are the first entries; starting at A1; where are I to put the formula; do I need to select the range; or?; call me 0419 431 649; Tony Clemenger or happy to email tonyATclemenger.me = respects & regards Tony.



HammettCarol


JJanette


ThompsonSandy


DjelassiRobyn


WebbKelly


BushRod


GrovesUrsula


CCarol


CiciullaSerge


CullumCoral


HookeyChad


RissonGarry


MelvilleScott


WilliamsRodney


MunroFiona


CraftJamie


BergincPeter


RowlandsScott


KoumidesMel


RavenMark


McGurganJustin


SymonsRycki


KellerEdward


BoydNeil


LibmanMark

[Aussiebear]

Welcome to the forum Tony. Firstly, putting a apostrophe in front of any string turns it into a comment rather than an instruction.

With Pike's suggestion, place the formulas into B1 & C1 respectively and change any references to A2 to A1, then copy down.

With snb's suggestion, copy the formula into B1 and enter with Control Shift Enter.

[clemenger]

I got the first one to work and understand the code; the second one, the array still fails to tick; BUT I am very happy with all and want to say THANKS.

[Aussiebear]

Yeah well snb likes to confound everyone. I guess his heart is in the right place though.

[clemenger]

Well said; is their any ACT! software training available at a reasonable price; in Melbourne or Australia?

[pike]

snb means the array formula
=LEFT(A1,MAX((N(CODE(MID(A1,ROW(OFFSET(A$1,,,LEN(A1),1)),1))<91)*ROW(OFFSET(A$1,,,LEN(A1),1))))-1)&" "&MID(A1,MAX((N(CODE(MID(A1,ROW(OFFSET(A$1,,,LEN(A1),1)),1))<91)*ROW(OFFSET(A$1,,,LEN(A1),1)))),LEN(A1))
it well worth a revisit