Well, Ron did the hard work on this one to calculate the number of dots
before the first number, but the same issue would be had with the ... being
read as ellipses instead of 3 dots, so, modifying Ron's formula would be as
follows:

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1," €¦","."&"."&".")&"0,1,2,3,4,5,6,7,8,9"))-1

Hope this helps. And be sure to click the little YES box below (and should
click for Ron and Roger as well :).
--
John C


"Jason" wrote:

John, thanks for catching that! Works great.

Can I hit you up for one mo How can I count the number of periods
BEFORE the first number (e.g. count periods from left until I hit a
number)?


THanks,
Jason

On Oct 9, 12:42 pm, John C <johnc@stateofdenial wrote:
Well, both Harlan and I missed part of your post that it could be 1 or 2
characters long, and Roger's works great, except for if there are 6 dots
(i.e.: 2 ellipses), after a single digit, it returns a value error.
I have modified Roger's formula slightly, that will take care of the issue,
it is still an array formula.

{=--(SUBSTITUTE(MID(SUBSTITUTE(A1,"€¦","."),MATCH(TRU E,ISNUMBER
(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),2),"."," "))}

--
John C

"Roger Govier" wrote:
Hi Jason

Try the following array entered formula
{=--(SUBSTITUTE(MID(A1,MATCH(TRUE,ISNUMBER
(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),2),"."," "))}

This is an array formula so Enter or edit using Control+Shift+Enter (CSE)
not just Enter.
Do not type the curly braces { } yourself, when you use CSE, Excel will
insert them for you.

The formula is all on one line, I split it so that your newsreader will not
break it in a strange place.
--
Regards
Roger Govier

"Jason" wrote in message
...
Hi,

I have a column of data that looks like
......0....
......0....
........2.2.
........2.2.
.11.2.2.2.2.2.2.2.2.
2.2.2.2.2.2.2....
.......2...2
.......2...
2.2.2.2.2.2.2.2.2.2.

I would like to extract the left-most number from each cell. How can
I do this with a formula? The number will be either one or two
digits.

Thanks,
Jason