Re: How to extract left-most number from a string

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

John, thanks for catching that! Works great.

Can I hit you up for one more: 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" <jal...@xxxxxxxx> wrote in message
news:187a90e1-61cc-48df-8230-00ff6b446ea8@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

.

Relevant Pages

  • Re: How to extract left-most number from a string
    ... there is a 'Was this post helpful to you?' ... John C ... , after a single digit, it returns a value error. ... Roger Govier ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How to extract left-most number from a string
    ... Thanks John. ... read as ellipses instead of 3 dots, so, modifying Ron's formula would be as ... , after a single digit, it returns a value error. ... Roger Govier ...
    (microsoft.public.excel.worksheet.functions)
  • Re: The Doctor rising
    ... And those dudes prolly came a lot. ... Picture John Wayne swaggering along saying "Partner, ... I fergive ya and ah'm not gonna hit ya. ... "I never meant to say that the Conservatives are generally stupid. ...
    (rec.arts.drwho)
  • Re: the most bizarre thing
    ... buddy (john) and i were playing a par 3 course in knoxville, ... come up on a maintenance crew on number 3 and then more workers on 18. ... at each other and he said you hollered i hit the ball and it goes over ...
    (rec.sport.golf)
  • Re: For Golfers Only - new golf book
    ... "John H" wrote in message ... Chapter 2 - How to Hit a Nike from the Rough When You Hit a Titleist from the ... Chapter 16 - Religion and the Meaning of the Birdie-to-Bogey Three Putt ... Chapter 18 - Proper Attire for the Effective and Undetected Use of the Foot ...
    (rec.boats)