Insert space where Non-Capitals meet Capitals

[jordan2322]

Hi Guys.

Im using the following validation code to generate a list that does not show duplicate data entries

Please Login or Register  to view this content.

This code works great but I want to have better formatting for the drop down validations that are created.

So what I want to achieve is a way to have this code modify the values displayed in the validation drop down list whenever a value appears that has small letters immediately proceeded with a capital letter. i.e. microsoftExcel.
I want to have this modified in the drop down selection but not the actual cells value from where the validation found the value, so that it inserts a Space before the capital letter E in the example. thus making the selection 'microsoft Excel'. is there a way in which we can do this?

for example:
A1: microsoftExcel..................................C1: (Possible selections would be) microsoft Excel, Windows, Computer, Mac
A2: Windows
A3: Computer
A4: microsoftExcel
A5: Mac

please see for more information and sample workbook: http://www.excelforum.com/excel-prog...uplicates.html

Is there a sensible way to achieve this or is there a more simplistic answer to my problem such as running a sub routine to insert the spaces before the validation box searches the column for the values..


kind regards

[JosephP]

add a function like this

Please Login or Register  to view this content.

then call that when building the string

Please Login or Register  to view this content.

btw using this method you are limited to about 255 letters in the validation string.

[jordan2322]

Thank you JosephP!