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(TRUE, 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