Using strings with ALL CAPS to split columns?

interr0bangr

New Member
Joined
Apr 8, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet that has multiple names within a single cell with single spaces between them. The first/middle names only have the first letter capitalized but the last names are in ALL caps.

For example cell A1 might include:

Joseph SMITH John Michael WILLIAMS Nancy BROWN Cameron MCCABE-WALKER & Maggie JONES

I'm struggling to find a way to separate each name into it's own column in the same row.

My best guess is to try to find the last character of any word in a string that is completely capitalized and then split it but it's much easier said than done based off my skillset. Any suggestions?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Welcome to the board. Try Data->Text to Columns then use space as the deliminating character to split with
 
Upvote 0
That was my first thought but it gets very messy as some people have middle names and such.

Ideally the output based off my example in the first post would be

Joseph SMITH
John Michael WILLIAMS
Nancy BROWN
Cameron MCCABE-WALKER
& Maggie JONES


There's several thousand rows in this spreadsheet that need to be split so I'm trying to avoid having to do too much manual cleanup if I can avoid it.
 
Upvote 0
This messy formula seems to do what you want:

Book1
ABCDEFGH
1Joseph SMITH John Michael WILLIAMS Nancy BROWN Cameron MCCABE-WALKER & Maggie JONESJoseph SMITHJohn Michael WILLIAMSNancy BROWNCameron MCCABE-WALKER& Maggie JONES 
Sheet13
Cell Formulas
RangeFormula
C1:H1C1=IFERROR(TRIM(LEFT(SUBSTITUTE($A1,TEXTJOIN(" ",1,$B1:B1),""),AGGREGATE(15,6,ROW(INDIRECT("2:999"))/(MID(SUBSTITUTE($A1,TEXTJOIN(" ",1,$B1:B1),"")&" ",ROW(INDIRECT("2:999")),1)=" ")/(ABS(CODE(MID(SUBSTITUTE($A1,TEXTJOIN(" ",1,$B1:B1),""),ROW(INDIRECT("1:998")),1))-77.5)<13),1))),"")


This can be modified for a vertical orientation if you want. However, this kind of thing is really better suited to a macro.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top