Split cell containing a lowercase letter followd by an uppercase letter with no space b/w

I have a column containing names & job titles an i am trying to split them into separate columns.

The issue is that the last letter (in lowercase) of the person's surname is immediately followed by the first letter (in uppercase) of the person's job title, but there is no space in between each. ie:

John BlackSales Manager
Sue RedSales Consultant
David GreenSales Rep

See image attached for what i would like it to look like.

If i can find a way of replacing this occurrence with a comma, then i can do a 'text to column'. The issue is that i do not know how to replace it with a comma (or some other unique symbol).

Any help would be great (greatly) appreciated.

Cheers
Rob

I presume its too much to expect that the "split" is always on the word Sales?

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

Do you have a list of departments?

hmm Can probably be done with VBA, but if you had a list of Depts, this would work...

	A	B	C	D
9	John BlackAccount Manager	John Black, Account Manager		Account Manager
10	Sue RedTechnology Consultant	Sue Red, Technology Consultant		Technology Consultant
11	David GreenSales Rep	David Green, Sales Rep		Sales Rep
12	Jack BlueFinance Manager	Jack Blue, Finance Manager		Finance Manager
13	Jason WhiteMarketing Consultant	Jason White, Marketing Consultant		Marketing Consultant
14	Amanda BrownSales Rep	Amanda Brown, Sales Rep		Sales Rep

B9=LEFT(A9,SEARCH($D$9:$D$16,$A9)-1)&", "&MID(A9,SEARCH($D$9:$D$16,$A9),99)
copied down

Try this array formula in B2 and copy down

=REPLACE(A2,MIN(IFERROR(FIND(CHAR(ROW($65:$90)),A2,FIND(" ",A2)+2),"")),,",")

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Row\Col	A	B
1	**Example of current state	**Desired state / outcome
2	**John BlackAccount Manager	John Black,Account Manager
3	**Sue RedTechnology Consultant	Sue Red,Technology Consultant
4	**David GreenSales Rep	David Green,Sales Rep
5	**Jack BlueFinance Manager	Jack Blue,Finance Manager
6	**Jason WhiteMarketing Consultant	Jason White,Marketing Consultant
7	**Amanda BrownSales Rep	Amanda Brown,Sales Rep

original outcome solution using CSE formula for 2nd formula

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Or if you want it split in two columns use this array formula

in B2 and pull formula to the right and down

=TRIM(MID(SUBSTITUTE(","&REPLACE($A2,LARGE(IFERROR(FIND(CHAR(ROW(A$65:A$90)),$A2),""),2),,", "),",",REPT(" ",50)),50*COLUMNS($A:A),50))

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Row\Col	A	B	C
1	Example of current state	Name	Title
2	John BlackAccount Manager	John Black	Account Manager
3	Sue RedTechnology Consultant	Sue Red	Technology Consultant
4	David GreenSales Rep	David Green	Sales Rep
5	Jack BlueFinance Manager	Jack Blue	Finance Manager
6	Jason WhiteMarketing Consultant	Jason White	Marketing Consultant
7	Amanda BrownSales Rep	Amanda Brown	Sales Rep

Originally Posted by AlKey

Try this array formula in B2 and copy down

=REPLACE(A2,MIN(IFERROR(FIND(CHAR(ROW($65:$90)),A2,FIND(" ",A2)+2),"")),,",")

Doesn't work on 3 word names if there might be any.

Edward Van HalenMusical Director

was looking at this...and Tony's additional about the space in Surname issue

i think the simplest/best option would be going back to Fdibbins one whereby you have a list of Titles to look up against
that way it really doesnt really matter how your name is spelt

However to answer your question
you could increase the section after " " to +4 like so

Please Login or Register  to view this content.

however this will cause issues with short surnames...like Ng

using the table of titles method

ashishmehra2010, what am I missing here? How would that break the names out, as requested?

Originally Posted by FDibbins

ashishmehra2010, what am I missing here? How would that break the names out, as requested?

It is a new feature that was introduced in Excel 2013. You do it manually once and excel remembers the pattern and repeats it.

https://support.office.com/en-us/art...2-c4dc84d49464

AlKey, cool. I tried it, but couldnt get it to work - kept telling me it couldnt see a pattern

Originally Posted by Tony Valko

Doesn't work on 3 word names if there might be any.

Edward Van HalenMusical Director

Is that what he's calling himself now?! And "Edward" doesn't quite have the same, wild ring to it somehow!

That's what DLR calls him.

They're going on tour!

Taking AlKey's formula that inserted a comma between the name and the Position, the names and positions can be separated into two columns this way (two names only)

In B2 Array enter and fill down:

Formula:

Please Login or Register  to view this content.

In C2 enter and fill down:

Formula:

Please Login or Register  to view this content.

Result:

	A	B	C
1	Example of current state	Name	Position
2	John BlackAccount Manager	John Black	Account Manager
3	Sue RedTechnology Consultant	Sue Red	Technology Consultant
4	David GreenSales Rep	David Green	Sales Rep
5	Jack BlueFinance Manager	Jack Blue	Finance Manager
6	Jason WhiteMarketing Consultant	Jason White	Marketing Consultant
7	Amanda BrownSales Rep	Amanda Brown	Sales Rep

If you want to make the real easy, use Find and Replace in Word.
Copy the Excel column into Word. It will be a Word table. When finished, copy back to Excel.

The number of names and initials doesn't matter. The following will place a comma followed by a space wherever a lower case letter is immediately followed by an upper case letter. If you don't want the space after the comma, leave it out of the Replace with statement.

In the Find what: enter ([a-z])([A-Z])
In the Replace with: enter \1, \2

Select Use wildcards
Replace All.