How to extract left-most number from a string

[Jason]

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

 

[John C]

=LEFT(SUBSTITUTE(SUBSTITUTE(A1,"…",""),".",""),1)

NOTE: I have the ... and the . because the ... is treated as an ellipses
unless specifically removed from the AutoCorrect feature.

Hope this helps.

 

[Harlan Grove]

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.

If the periods are just placeholders for other characters, try this.

=MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&" 0123456789"),LEN(A1)+1),1)

This does NOT need to be entered as an array formula.

 

[Roger Govier]

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.

 

[Roger Govier]

Hi Harlan

I think perhaps you missed the OP's point about the numbers being 1 or 2
digits.
Row 5 of his test data has the number 11

 

[John C]

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),".",""))}

 

[Jason]

Hi all--
Thanks so much for the quick responses. Roger, your formula works,
thanks!!

John & Harlan, both of your formulas work with the exception of
getting the 11 (two digit number) incorrect. They return a 1 when it
should be an 11. I do appreciate the quick help!

Jason

 

[Jason]

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

 

[Ron Rosenfeld]

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

If you should happen to install, or have installed, Longre's free morefunc.xll
add-in (from http://xcell05.free.fr/ )

Then to get the first digit(s), you could use:

=REGEX.MID(A1,"\d+") or if you want it as a number,

=--REGEX.MID(A1,"\d+")

and to get the count of the number of dots before the first digit:

=LEN(REGEX.MID(A1,"^\.*"))


If you prefer native functions, then for the digits:


=INT(LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))

and for the number of dots before the first digit:

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

--ron

 

[John C]

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 .

 

[Jason]

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

Jason

 

[John C]

Oh, I guess it depends on where you access the groups from. In the Microsoft
site, there is a 'Was this post helpful to you?' at the bottom that you can
check yes or no.

Glad it's working for you.

 

[Roger Govier]

Hi Jason

You could just extract part of my array formula

{=MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1}
Still entered as an array formula

This is not effected by the ellipse ...

 

[Roger Govier]

Nice catch John.
I hadn't considered the possibility of a number followed by 6 periods (2
ellipse).
It does not matter if they come before the first number in the value.

 

[John C]

Actually, it still is. For example, I typed 7 dots before a digit in cell A1,
the first 6 of which turn in to 2 ellipses. Your formula calculates to 3, not
7.

 

[Harlan Grove]

I think perhaps you missed the OP's point about the numbers being 1 or 2
digits.
Row 5 of his test data has the number 11 ....

Good point. Change the formula to

=LOOKUP(100,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"
0123456789"),LEN(A1)+1),{1,2}))&""

 

[Roger Govier]

Very nice, Harlan.

--
Regards
Roger Govier

Good point. Change the formula to

=LOOKUP(100,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"
0123456789"),LEN(A1)+1),{1,2}))&""