Text to Columns by Capital Letters

Here's one way. there must be a better formula, though!! This is an array formula, and a bit of a MONSTER.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

Formula:

Please Login or Register  to view this content.

If all names consist of two words it can be done with a formula but if names are more complicated you would need VBA.
Here is an formula example:
Enter formula in B1 and copy down

Formula:

Please Login or Register  to view this content.

v	A	B
1	JohnBower	John Bower
2	GeorgioArmani	Georgio Armani

Edit: (Updated by request from Tony Valko) There maybe a situation where the first capital letter of the first name and the last name are the same. in this case formula may return an error or incorrect results. To avoid this use formula below

Formula:

Please Login or Register  to view this content.

not directly

this should answer your problems though
https://www.extendoffice.com/documen...al-letter.html

This might be of use. It is an array formula so enter with Ctrl + Shift + Enter

Formula:

Please Login or Register  to view this content.

	A	B
1	JohnJackson	John Jackson
2	DaveBrown	Dave Brown
3	SueDavis	Sue Davis

Originally Posted by AlKey

=REPLACE(A1,AGGREGATE(15,6,FIND(CHAR(ROW(A$65:A$90)),RIGHT(A1,LEN(A1)-1)),1)+1,," ")

I'll give ya some rep if you change this:

ROW(A$65:A$90)

To this:

ROW(INDIRECT("65:90"))

Why not just use this? Enter with Ctrl + Shift + Enter

Formula:

Please Login or Register  to view this content.

ROW($65:$90)

@Tony Valko
LOL! Updated as requested in my original reply

Originally Posted by AlKey

@Tony Valko
LOL! Updated as requested in my original reply

The first formula needs to be changed also!

Originally Posted by Tony Valko

The first formula needs to be changed also!

Done!!!!!!!!

Originally Posted by newdoverman

Why not just use this? Enter with Ctrl + Shift + Enter
=REPLACE(A1,AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),A1),2),," ")

ROW($65:$90)

That doesn't need array entry.

Try that on this name: AmyAdams

AlKey's other formula would work except for the ROW($65:$90)...

A1 = AmyAdams

=REPLACE(A1,AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),RIGHT(A1,LEN(A1)-1)),1)+1,," ")

Correctly returns Amy Adams.

However, insert a new row 2 and see what happens.

Originally Posted by AlKey

Done!!!!!!!!

Very nice!

Here ya go...

Thank you Tony!

Well deserved!

I get a #NUM! error no matter which formula I use for the name AmyAdams if I substitute a name that exists in a list with that name.

	A	B	C	D	E	F	G	H	I	J
4	TomJones			Tom Jones		Tom Jones		Tom Jones		Tom Jones
5	MarySmith			Mary Smith		Mary Smith		Mary Smith		Mary Smith
6	JoeBlow			Joe Blow		Joe Blow		Joe Blow		Joe Blow
7	AmyAdams			#NUM!		#NUM!		#NUM!		Amy Adams
8	JaneDoe			Jane Doe		Jane Doe		Jane Doe		Jane Doe

First formula =REPLACE(A1,AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),A1),2),," ")
Second formula =REPLACE(A1,AGGREGATE(15,6,FIND(CHAR(ROW(INDIRECT("65:90"))),A1),2),," ")
Third formula =REPLACE(A1,AGGREGATE(15,6,FIND(CHAR(ROW(A$65:A$90)),A1),2),," ")

This one that you supplied does work when substituting as above
Fourth forumula =REPLACE(A1,AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),RIGHT(A1,LEN(A1)-1)),1)+1,," ")

Been playing around with the problem. This is the latest that I have come up with using what has been proven successful.

Formula:

Please Login or Register  to view this content.