|
Finding Numbers with Cells that also contain letters
Hi All,
I have a column of data, the cells are filled with numbers and comments of text, they do not consistantly follow suit i.e the numbers are at the beginning of the cell necessarily. I want to be able to find where the numbers are and extract them into another field. Does anyone know of a formula that can do this? -- Adam ----------- Windows 98 + Office Pro 97 |
Hi, Adam-
I know it can be done with VBA, but I'm not sure about a formula. If you get no answers feel free to contact me at Cyclezen(at)yahoo(d0t)com. The VBA code is simple and direct. Dave O |
Hi
see: http://www.dicks-blog.com/archives/2...t-1/trackback/ -- Regards Frank Kabel Frankfurt, Germany Adam wrote: Hi All, I have a column of data, the cells are filled with numbers and comments of text, they do not consistantly follow suit i.e the numbers are at the beginning of the cell necessarily. I want to be able to find where the numbers are and extract them into another field. Does anyone know of a formula that can do this? |
Slightly shorter ... very slightly!
This is *without* a named formula. =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)))) Also an array formula. and must be entered with CSE (<Ctrl <Shift <Enter). -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Frank Kabel" wrote in message ... Hi see: http://www.dicks-blog.com/archives/2...t-1/trackback/ -- Regards Frank Kabel Frankfurt, Germany Adam wrote: Hi All, I have a column of data, the cells are filled with numbers and comments of text, they do not consistantly follow suit i.e the numbers are at the beginning of the cell necessarily. I want to be able to find where the numbers are and extract them into another field. Does anyone know of a formula that can do this? |
Hi RD
also nice alternative. Even shorter (2 characters <vbg): =--MID(A1,MATCH(FALSE,ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)))) -- Regards Frank Kabel Frankfurt, Germany RagDyeR wrote: Slightly shorter ... very slightly! This is *without* a named formula. =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)))) Also an array formula. and must be entered with CSE (<Ctrl <Shift <Enter). -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Frank Kabel" wrote in message ... Hi see: http://www.dicks-blog.com/archives/2...t-1/trackback/ Adam wrote: Hi All, I have a column of data, the cells are filled with numbers and comments of text, they do not consistantly follow suit i.e the numbers are at the beginning of the cell necessarily. I want to be able to find where the numbers are and extract them into another field. Does anyone know of a formula that can do this? |
Hi Frank,
This formula doesnt seem to be working for me. I'm just copying and pasting the formula you've specified and pressing ctrl, shift and enter. Am I doing something wrong? Not sure what the "--" signs are in the formula, is that correct? "Frank Kabel" wrote: Hi RD also nice alternative. Even shorter (2 characters <vbg): =--MID(A1,MATCH(FALSE,ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)))) -- Regards Frank Kabel Frankfurt, Germany RagDyeR wrote: Slightly shorter ... very slightly! This is *without* a named formula. =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)))) Also an array formula. and must be entered with CSE (<Ctrl <Shift <Enter). -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Frank Kabel" wrote in message ... Hi see: http://www.dicks-blog.com/archives/2...t-1/trackback/ Adam wrote: Hi All, I have a column of data, the cells are filled with numbers and comments of text, they do not consistantly follow suit i.e the numbers are at the beginning of the cell necessarily. I want to be able to find where the numbers are and extract them into another field. Does anyone know of a formula that can do this? |
The -- converts true/falses to 1/0's. The right most negative sign converts the
true/falses to -1/0 and the left changes the sign to +1/0. But Frank's formula worked for me. Try putting it all on one line in the formula bar or paste this version (broken at a different spot): =--MID(A1,MATCH(FALSE,ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)),0), 100-SUM(--ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)))) But it's still array entered in one cell. Adam wrote: Hi Frank, This formula doesnt seem to be working for me. I'm just copying and pasting the formula you've specified and pressing ctrl, shift and enter. Am I doing something wrong? Not sure what the "--" signs are in the formula, is that correct? "Frank Kabel" wrote: Hi RD also nice alternative. Even shorter (2 characters <vbg): =--MID(A1,MATCH(FALSE,ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)))) -- Regards Frank Kabel Frankfurt, Germany RagDyeR wrote: Slightly shorter ... very slightly! This is *without* a named formula. =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)))) Also an array formula. and must be entered with CSE (<Ctrl <Shift <Enter). -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Frank Kabel" wrote in message ... Hi see: http://www.dicks-blog.com/archives/2...t-1/trackback/ Adam wrote: Hi All, I have a column of data, the cells are filled with numbers and comments of text, they do not consistantly follow suit i.e the numbers are at the beginning of the cell necessarily. I want to be able to find where the numbers are and extract them into another field. Does anyone know of a formula that can do this? -- Dave Peterson |
And if that doesn't work, post the string you have in A1 that made the formula
fail. Adam wrote: Hi Frank, This formula doesnt seem to be working for me. I'm just copying and pasting the formula you've specified and pressing ctrl, shift and enter. Am I doing something wrong? Not sure what the "--" signs are in the formula, is that correct? "Frank Kabel" wrote: Hi RD also nice alternative. Even shorter (2 characters <vbg): =--MID(A1,MATCH(FALSE,ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(-MID(A1,ROW(INDIRECT("1:100")),1)))) -- Regards Frank Kabel Frankfurt, Germany RagDyeR wrote: Slightly shorter ... very slightly! This is *without* a named formula. =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM (--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)))) Also an array formula. and must be entered with CSE (<Ctrl <Shift <Enter). -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Frank Kabel" wrote in message ... Hi see: http://www.dicks-blog.com/archives/2...t-1/trackback/ Adam wrote: Hi All, I have a column of data, the cells are filled with numbers and comments of text, they do not consistantly follow suit i.e the numbers are at the beginning of the cell necessarily. I want to be able to find where the numbers are and extract them into another field. Does anyone know of a formula that can do this? -- Dave Peterson |
| All times are GMT +1. The time now is 09:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com