|
Removing characters from a cell (keeping only numbers) 2
Hello..
I did post this question yesterday and following a fantastic response I thought I had it rapid up.. However the cool formula provided has a slight defect to it.. Ok.. The problem..., I have a vast list of cells with numbers with in them (which is good) however the string of numbers sometimes commence with the character and sometimes may end with a character or both in some cases, (which is bad). Example A1: 1234435999 A2: 34566673341 A3: 444532322711 It appears completely random whether the cells contain any character or is completely free from the little blighters. So I need a formula (rather then macro) to remove all the characters in one go (rather then using the longwinded Left / Right formula) Yesterday I was given the following impressive formula¦ B1 =LEFT(MID(P29,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},P29 &"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9}," 0123456789"&P29))),SUMPRODUCT(--ISNUMBER(--MID(P29,ROW(INDIRECT("1:"&(LEN(P29)))),1)))) This appeared to work perfectly, however I have since noticed if the cell ENDS with any consecutive digits (ie. 35999) the formula only appears to generate one of the digits (359). Can anyone amend the formula or provide a new one to get around my dilemma ..? Many thanks, Monk |
Removing characters from a cell (keeping only numbers) 2
On Tue, 24 Jan 2006 04:12:01 -0800, "Monk"
wrote: Hello.. I did post this question yesterday and following a fantastic response I thought I had it rapid up.. However the cool formula provided has a slight defect to it.. Ok.. The problem..., I have a vast list of cells with numbers with in them (which is good) however the string of numbers sometimes commence with the character and sometimes may end with a character or both in some cases, (which is bad). Example A1: 1234435999 A2: 34566673341 A3: 444532322711 It appears completely random whether the cells contain any character or is completely free from the little blighters. So I need a formula (rather then macro) to remove all the characters in one go (rather then using the longwinded Left / Right formula) Yesterday I was given the following impressive formula B1 =LEFT(MID(P29,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},P2 9&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9}, "0123456789"&P29))),SUMPRODUCT(--ISNUMBER(--MID(P29,ROW(INDIRECT("1:"&(LEN(P29)))),1)))) This appeared to work perfectly, however I have since noticed if the cell ENDS with any consecutive digits (ie. 35999) the formula only appears to generate one of the digits (359). Can anyone amend the formula or provide a new one to get around my dilemma ..? Many thanks, Monk The REGEX formula I posted yesterday will handle all that. --ron |
Removing characters from a cell (keeping only numbers) 2
Hi Ron,
I did try this but unable to complete the download ("keeps saying Compressed (zipped) folder is invalid or corrupt").. This may have something to do with our security protection preventing the download (not sure). Also if I did manage to download and use the extra formulas, would everyone who wishes to use the spreadsheet have to do the same..? Thanks, Monk "Ron Rosenfeld" wrote: On Tue, 24 Jan 2006 04:12:01 -0800, "Monk" wrote: Hello.. I did post this question yesterday and following a fantastic response I thought I had it rapid up.. However the cool formula provided has a slight defect to it.. Ok.. The problem..., I have a vast list of cells with numbers with in them (which is good) however the string of numbers sometimes commence with the character and sometimes may end with a character or both in some cases, (which is bad). Example A1: 1234435999 A2: 34566673341 A3: 444532322711 It appears completely random whether the cells contain any character or is completely free from the little blighters. So I need a formula (rather then macro) to remove all the characters in one go (rather then using the longwinded Left / Right formula) Yesterday I was given the following impressive formula¦ B1 =LEFT(MID(P29,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},P2 9&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9}, "0123456789"&P29))),SUMPRODUCT(--ISNUMBER(--MID(P29,ROW(INDIRECT("1:"&(LEN(P29)))),1)))) This appeared to work perfectly, however I have since noticed if the cell ENDS with any consecutive digits (ie. 35999) the formula only appears to generate one of the digits (359). Can anyone amend the formula or provide a new one to get around my dilemma ..? Many thanks, Monk The REGEX formula I posted yesterday will handle all that. --ron |
Removing characters from a cell (keeping only numbers) 2
Assuming your unwanted character is an a and that your data is in column A starting in row 1, type this formula into cell B1 and copy it down. This deals with all 4 possibilities a 77999a a77999 77999a 77999 IF(LEFT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a",IF(RI GHT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a",MID(a1,2, LEN(a1)-2),MID(a1,2,LEN(a1)-1)),IF(RIGHT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a", LEFT(a1,LEN(a1)-1),a1)) I have posted this so that you can see how to deal with numbers and text strings of varying length. Bobf -- bob777 ------------------------------------------------------------------------ bob777's Profile: http://www.excelforum.com/member.php...o&userid=28504 View this thread: http://www.excelforum.com/showthread...hreadid=504430 |
Removing characters from a cell (keeping only numbers) 2
On Tue, 24 Jan 2006 06:08:03 -0800, "Monk"
wrote: Hi Ron, I did try this but unable to complete the download ("keeps saying Compressed (zipped) folder is invalid or corrupt").. This may have something to do with our security protection preventing the download (not sure). That's wierd. I've never had a problem downloading. Also if I did manage to download and use the extra formulas, would everyone who wishes to use the spreadsheet have to do the same..? There is a selectable option to include morefunc "in the workbook" so that would not be necessary when you distribute it. However, you could also use a fairly simple UDF to extract the digits. And this, too, would be distributed with the workbook. (But there are a bunch of other useful functions in morefunc). 1. <alt<F11 opens the VB Editor 2. Ensure your project is selected in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. 3. Use the formula by entering =Digits(cell_ref) or =Digits("string" into some cell. 4. The formula returns the digits as a string (text). If you want them returned as a number, change the first line to Function Digits(str As String) As Double However, this may give unexpected results depending on the cell formatting (i.e. with large values and General formatting, Excel may give you Scientific notation). ========================= Option Explicit Function Digits(str As String) As String Dim i As Long Dim t As String For i = 1 To Len(str) If Mid(str, i, 1) Like "[0-9]" Then t = t & Mid(str, i, 1) End If Next i Digits = t End Function =========================== Thanks, Monk "Ron Rosenfeld" wrote: On Tue, 24 Jan 2006 04:12:01 -0800, "Monk" wrote: Hello.. I did post this question yesterday and following a fantastic response I thought I had it rapid up.. However the cool formula provided has a slight defect to it.. Ok.. The problem..., I have a vast list of cells with numbers with in them (which is good) however the string of numbers sometimes commence with the character and sometimes may end with a character or both in some cases, (which is bad). Example A1: 1234435999 A2: 34566673341 A3: 444532322711 It appears completely random whether the cells contain any character or is completely free from the little blighters. So I need a formula (rather then macro) to remove all the characters in one go (rather then using the longwinded Left / Right formula) Yesterday I was given the following impressive formula B1 =LEFT(MID(P29,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},P2 9&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9}, "0123456789"&P29))),SUMPRODUCT(--ISNUMBER(--MID(P29,ROW(INDIRECT("1:"&(LEN(P29)))),1)))) This appeared to work perfectly, however I have since noticed if the cell ENDS with any consecutive digits (ie. 35999) the formula only appears to generate one of the digits (359). Can anyone amend the formula or provide a new one to get around my dilemma ..? Many thanks, Monk The REGEX formula I posted yesterday will handle all that. --ron --ron |
Removing characters from a cell (keeping only numbers) 2
Cool..,
This new formula is perfect.. Many thanks to all (Again) Monk :0) "bob777" wrote: Assuming your unwanted character is an a and that your data is in column A starting in row 1, type this formula into cell B1 and copy it down. This deals with all 4 possibilities a 77999a a77999 77999a 77999 IF(LEFT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a",IF(RI GHT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a",MID(a1,2, LEN(a1)-2),MID(a1,2,LEN(a1)-1)),IF(RIGHT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a", LEFT(a1,LEN(a1)-1),a1)) I have posted this so that you can see how to deal with numbers and text strings of varying length. Bobf -- bob777 ------------------------------------------------------------------------ bob777's Profile: http://www.excelforum.com/member.php...o&userid=28504 View this thread: http://www.excelforum.com/showthread...hreadid=504430 |
| All times are GMT +1. The time now is 10:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com