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

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

Thanks John. I don't see a "yes" box anywhere, but I did rate the
posts 5 stars.

Jason

On Oct 9, 1:36 pm, John C <johnc@stateofdenial> wrote:
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 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: A proposal to toll I-95 and I-85 in Virginia/North Carolina
    ... >>Within that world - the design and location of a proposed road often ... folks don't ... this is not something that the DOTs should ... ... I could say that about transit John... ...
    (misc.transport.road)
  • 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
    ... John, thanks for catching that! ... Can I hit you up for one more: How can I count the number of periods ... , after a single digit, it returns a value error. ... "Roger Govier" wrote: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Seeking old tile
    ... John wrote: ... My tile is a beige background with brownish dots. ... mosiac-like shades of pink, while mine is in shades of brown. ...
    (alt.home.repair)
  • Re: Update Query Question!
    ... So you want to strip out the existing dots from the First Name? ... Allen Browne - Microsoft MVP. ... > I have a name John Smith when I do the replace, ...
    (microsoft.public.access.queries)