How to find position of capital letter in a text string?

Thanks for your quick replies!

@Odlchippy, I will try that out.

@VBA Noob:

A typical dataset would look like that:

1Mountain (IRE)Jonjo OŽNeill 4 10-12A P McCoy SP 1.83 fav
2 1 Counting House (IRE)J A B Old 4 10-12 Jason Maguire SP 41
3 3 Ned Ludd (IRE)J G Portman 4 11-3 Robert Thornton SP 4.5
4 7 Pommerol (FR)F Doumen 4 11-0 t A Duchene(3) SP 5.5
5 2 Dynamic Rhythm (USA)G Brown 4 10-5 Miss L Horner(7) SP 67
6 13 GabierG L Moore 4 10-12 Jamie Moore SP 21
7 5 Kinetic Power (IRE)N J Hawke 4 10-7 Mr J Snowden(5) SP 101
8 3 Pepito Collonges (FR)Mrs L J Mongan 4 10-12 Leighton Aspell SP 67
9 6 HonourŽs Dream (FR)T R George 4 10-12 Wayne Hutchinson SP 26
10 26 Moonlight Music (IRE)K C Bailey 4 10-5 Alan OŽKeeffe SP 101
F Sir Rique (FR)P J Hobbs 4 10-12 Richard Johnson SP 15
PU Steak N Kidney (USA)M Wigham 4 10-12 Joe Tizzard SP 101


This is from horse racing.

My final target is to create data set, which includes horse name and price:

Mountain 1.83
Counting House 41
Ned Ludd 4.5
Pommerol 5.5
.
.
.
Steak N Kidney 101

I was thinking, since Excel can change lower case to upper case in a function , it should have a method somehow to identify capital letters.

Thanks for your help.

Cheers,
hegisin

Hi old chippy!

I tried your method and it really works. I have to study the formular a little bit more in detail in order to understand why it works, though

However, I'm still left with one problem. After I have converted a set of data, I still have to sort it. Unfortunately Excel does not allow me to do so with this set.

Any further trick that I could use here?

Many thanks for your kind help.

Cheers,
hegisin

This formula will give you the first capital letter in A1

=MID(A1,MIN(FIND(CHAR(ROW(INDIRECT("65:90"))),A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),1)

confirmed with CTRL+SHIFT+ENTER

Hi all,

First of all I have to say that I'm quite impressed with this forum and very fast response.

I certainly will use it frequently from now on.

@ VBA Noob

Very cool add-on functions. Thanks for that. I will fine tune the formula now according to my requirements.

@ daddylonglegs

The formula is working fine if I want to do the search within one particular cell. But how would I make it work for a dataset (lets say a1:a10)?

Also please let me know how to modidy the formula, if I wanted to know just the position number of the capital letter within the string.

Thanks all!

hegisin