Excel -- Text to columns, no delimiters

[avandale]

Hi,

I have a spreadsheet with 28000+ Merchant Names that need to be grouped by their Company. The trouble is, there are no spaces between the individual words the make up the listing e.g.

VodafoneDublin
VodafoneGalway
VodafoneNewYork
VodafoneSydney

etc.

There are about 500+ merchants, each with an average of 30-60 different listings.

What I'm trying to do is to seperate the phrases at the point where they dont match. i.e. in this instance, where the phrase Vodafone ends. I know that there is a line of code/formula that allows me to work with each individual character in a phrase, but I'm totally stumped!!!


Any ideas?

Thanks in advance!

 

[chandlm]

Hi Avandale,

Will it always be the same format as in your code where the company will always start with a capital letter and the office will also start with a capital letter.



Matt

 

[BrainB]

Here is something that may work.

Make another column with a formula like this :-
=LEFT(A1,4)
Which puts extracts the first 4 characters. You can then sort/filter etc. on this column.

If it is the end you want, then
=RIGHT(A1,4)


Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[KenWright]

Do you have a unique list of the companies that will be found in that data?

Regards
Ken...................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------