Is there a code for splitting cells in Excel without a specific deliminater?

Question

I have a list of contact details in Excel listed like this:

Row 1 - FS
Row 2 - Firstname Surname
Row 3 - Job roleCompany name

I want to create a 4th row where the "Company name" is listed, rather than compounded to row 3.

I have tried the Deliminators option, however this only works using a space or comma etc. I am unsure how to split a cell when the word has been formatted this way.

Is there a way of coding this?

*I have not got the original document to re copy/paste into Excel. There are 900 pages of this data - please help me avoid doing the space by hand!

Thank you

Is there a chance there is a zero-width character between the two words? — cybernetic.nomad, Jan 29 '22 at 13:17
How many different company names are on your list? And do you have a separate list of company names? — bugdrown, Jan 29 '22 at 13:18
You can easily code it using Power Query, available in Windows Excel 2010+ and Excel 365. There is an option to Split on a change from lower to upper case. — Ron Rosenfeld, Jan 29 '22 at 18:50

score 1 · Answer 1 · answered Jan 29 '22 at 13:05

1

Unfortunately, without a delimiter there is not really a way to tell Excel or any other program where the job title ends and the company name starts.

So you are going to have to do some manual work, but there may be ways for you to save time. There is probably some repetition in job titles across your data, with people with the same job title in different companies for ex. So for each job title, you can search and replace the text to add a delimiter at the end.

For ex: Search and replace "Account Director" with "Account Director," so that "Account DirectorCompany Name" becomes "Account Director,Company Name".

You would have to do this once per unique job title, but probably not 900. Then you have a delimiter you can use to split.

answered Jan 29 '22 at 13:05

Pierre Lejay

66
3

A search for the last word in most common job titles would likely cover more ground faster (ex.: "Manager" would cover both "Sales Manager" and "Account Manager") – cybernetic.nomad Jan 29 '22 at 13:17
Yes there is - can't you split according to fixed number of chars? (i.e. w/delimitter)? – JB-007 Jan 29 '22 at 22:31
– JB-007 Jan 29 '22 at 22:31

Solar Mike · Accepted Answer · 2022-01-29T13:48:20.957

Well, came up with this:

=SMALL(IFERROR(FIND({"A","B","C","W"},A1,2),""),1)

Only had a selection of letters, the "M" is missing because of manager, but you could use an if() to escape that if needed.

Find() recognizes the difference between upper and lower case... Small() takes the first result as many letters could be matched as find() is set to start looking at position2 to avoid the first capital in the string.

Not an easy task and I have work to do, but was interesting so far.

Note, if the adding a "," suggestion works, then the following will work:

=mid(A1,find(",",A1,1)+1,60)

only used 60 to get however many characters remain.

Is there a code for splitting cells in Excel without a specific deliminater?

2 Answers2

Linked