|
How to extract left-most number from a string
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 |
How to extract left-most number from a string
=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. -- John C "Jason" wrote: 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 |
How to extract left-most number from a string
Jason wrote...
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. |
How to extract left-most number from a string
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 |
How to extract left-most number from a string
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 -- Regards Roger Govier "Harlan Grove" wrote in message ... Jason wrote... 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. |
How to extract left-most number from a string
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 |
How to extract left-most number from a string
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 On Oct 9, 12:12*pm, "Roger Govier" <roger@technology4unospamdotcodotuk 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 |
How to extract left-most number from a string
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 |
How to extract left-most number from a string
On Thu, 9 Oct 2008 08:20:43 -0700 (PDT), Jason wrote:
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 |
How to extract left-most number from a string
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 |
How to extract left-most number from a string
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 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 |
How to extract left-most number from a string
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. :) -- John C "Jason" wrote: 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 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 |
How to extract left-most number from a string
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 ... -- Regards Roger Govier "Jason" wrote in message ... 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 |
How to extract left-most number from a string
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. -- Regards Roger Govier "John C" <johnc@stateofdenial wrote in message ... 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 |
How to extract left-most number from a string
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. -- John C "Roger Govier" wrote: 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 ... -- Regards Roger Govier "Jason" wrote in message ... 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 |
How to extract left-most number from a string
"Roger Govier" <roger@technology4unospamdotcodotuk wrote...
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 .... "Harlan Grove" wrote in message .... =MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&" 0123456789"),LEN(A1)+1),1) 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}))&"" |
How to extract left-most number from a string
Very nice, Harlan.
-- Regards Roger Govier "Harlan Grove" wrote in message ... "Roger Govier" <roger@technology4unospamdotcodotuk wrote... 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 ... "Harlan Grove" wrote in message ... =MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&" 0123456789"),LEN(A1)+1),1) 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}))&"" |
| All times are GMT +1. The time now is 04:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com