Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Masking out characters in a cell
How should one mask out unwanted characters in a range of cells or extranct
the wanted character set within a cell to another cell? Source cell: 123ABC Desired data to be extracted: numbers only Which function should be used to either mask out the unwanted letters and leave the numbers or extract to another (set of) cells by filtering by position within the source cell or by the character type desired? -- Sincerely, Billiamm |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Masking out characters in a cell
Are there always 3 digits in front? If so (assuming A1 is the source
cell)... =LEFT(A1,3) If not a constant number of digits, are the digits always in front? If so, this is one way... =LEFT(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))) Something else? Tell us what. Rick "dnardi" wrote in message ... How should one mask out unwanted characters in a range of cells or extranct the wanted character set within a cell to another cell? Source cell: 123ABC Desired data to be extracted: numbers only Which function should be used to either mask out the unwanted letters and leave the numbers or extract to another (set of) cells by filtering by position within the source cell or by the character type desired? -- Sincerely, Billiamm |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Masking out characters in a cell
I found the function on this DG a while back:
Function reNums(str As String) Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "\D" reNums = re.Replace(str, "") End Function Regards, Ryan--- -- RyGuy "Rick Rothstein (MVP - VB)" wrote: Are there always 3 digits in front? If so (assuming A1 is the source cell)... =LEFT(A1,3) If not a constant number of digits, are the digits always in front? If so, this is one way... =LEFT(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))) Something else? Tell us what. Rick "dnardi" wrote in message ... How should one mask out unwanted characters in a range of cells or extranct the wanted character set within a cell to another cell? Source cell: 123ABC Desired data to be extracted: numbers only Which function should be used to either mask out the unwanted letters and leave the numbers or extract to another (set of) cells by filtering by position within the source cell or by the character type desired? -- Sincerely, Billiamm |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Masking out characters in a cell
On Mon, 17 Dec 2007 22:36:01 -0800, dnardi wrote:
How should one mask out unwanted characters in a range of cells or extranct the wanted character set within a cell to another cell? Source cell: 123ABC Desired data to be extracted: numbers only Which function should be used to either mask out the unwanted letters and leave the numbers or extract to another (set of) cells by filtering by position within the source cell or by the character type desired? If the numbers are consecutive: =LOOKUP(9.99999999999999E+307,--MID(A1,MIN( SEARCH({0,1,2,3,4,5,6,7,8,9},A1& "0123456789")), ROW(INDIRECT("1:"&LEN(A1))))) If the numbers are not consecutive, I would use a VBA UDF. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Masking out characters in a cell
Which function should be used to either mask out the unwanted letters and
leave the numbers or extract to another (set of) cells by filtering by position within the source cell or by the character type desired? If the numbers are consecutive: =LOOKUP(9.99999999999999E+307,--MID(A1,MIN( SEARCH({0,1,2,3,4,5,6,7,8,9},A1& "0123456789")), ROW(INDIRECT("1:"&LEN(A1))))) Here is a non-volatile formula which will do the same thing... =--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))) Rick |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Masking out characters in a cell
That's susceptible to row insertions.
ROW($1:$999) So, it's a trade-off making a particular suggestion. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... Which function should be used to either mask out the unwanted letters and leave the numbers or extract to another (set of) cells by filtering by position within the source cell or by the character type desired? If the numbers are consecutive: =LOOKUP(9.99999999999999E+307,--MID(A1,MIN( SEARCH({0,1,2,3,4,5,6,7,8,9},A1& "0123456789")), ROW(INDIRECT("1:"&LEN(A1))))) Here is a non-volatile formula which will do the same thing... =--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))) Rick |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Masking out characters in a cell
Well, true, but (unless I am missing something) not in any way that will
affect the formula's returned value. I also probably should have mentioned in my first posting that the 999 is a general value and is there to protect against longish text entries... it makes sure all characters (up to a maximum of 999) are examined. If the text/number/text entries being processed are known to be no more than, say, a maximum of 20 characters long, then 20 can be substituted for the 999. Rick "T. Valko" wrote in message ... That's susceptible to row insertions. ROW($1:$999) So, it's a trade-off making a particular suggestion. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... Which function should be used to either mask out the unwanted letters and leave the numbers or extract to another (set of) cells by filtering by position within the source cell or by the character type desired? If the numbers are consecutive: =LOOKUP(9.99999999999999E+307,--MID(A1,MIN( SEARCH({0,1,2,3,4,5,6,7,8,9},A1& "0123456789")), ROW(INDIRECT("1:"&LEN(A1))))) Here is a non-volatile formula which will do the same thing... =--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))) Rick |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Masking out characters in a cell
I also probably should have mentioned in my first posting that the 999 is
a general value and is there to protect against longish text entries... it makes sure all characters (up to a maximum of 999) are examined. If the text/number/text entries being processed are known to be no more than, say, a maximum of 20 characters long, then 20 can be substituted for the 999. .... although to protect against row deletions, the number should be larger than the maximum number of characters to be processed (using some reasonable cushion). Rick |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Masking out characters in a cell
Try this:
A1 = 123abc B1 = your formula Insert a new row 1 and see what happens. ROW($1:$999) becomes ROW($2:$1000) So this now starts at the 2nd char and the result of the SUMPRODUCT is 1 char less than it should be MID(A2,ROW($2:$1000),1) Formula result: 12 I'm just being especially anal today! I should be ok once I get some sugar in my system! -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... Well, true, but (unless I am missing something) not in any way that will affect the formula's returned value. I also probably should have mentioned in my first posting that the 999 is a general value and is there to protect against longish text entries... it makes sure all characters (up to a maximum of 999) are examined. If the text/number/text entries being processed are known to be no more than, say, a maximum of 20 characters long, then 20 can be substituted for the 999. Rick "T. Valko" wrote in message ... That's susceptible to row insertions. ROW($1:$999) So, it's a trade-off making a particular suggestion. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... Which function should be used to either mask out the unwanted letters and leave the numbers or extract to another (set of) cells by filtering by position within the source cell or by the character type desired? If the numbers are consecutive: =LOOKUP(9.99999999999999E+307,--MID(A1,MIN( SEARCH({0,1,2,3,4,5,6,7,8,9},A1& "0123456789")), ROW(INDIRECT("1:"&LEN(A1))))) Here is a non-volatile formula which will do the same thing... =--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))) Rick |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Masking out characters in a cell
See inline comments....
Try this: A1 = 123abc B1 = your formula Insert a new row 1 and see what happens. ROW($1:$999) becomes ROW($2:$1000) So this now starts at the 2nd char and the result of the SUMPRODUCT is 1 char less than it should be MID(A2,ROW($2:$1000),1) Formula result: 12 I was concentrating so on the second number changing (for rows no where near the beginning row), that I completely forgot about the first number changing too. This "repair" should (unless I missed something else<g) do what the OP wanted and still be non-volatile... =--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SUMPRODUCT(--ISNUMBER(--MID(REPT(" ",999-LEN(A1))&A1,ROW($1:$999),1)))) I'm just being especially anal today! I should be ok once I get some sugar in my system! Sugar? Not coffee? Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Characters in cell | Excel Discussion (Misc queries) | |||
Transfer specific characters from cell to another cell. | New Users to Excel | |||
masking credit card info when worksheet is printed | Excel Discussion (Misc queries) | |||
Display masking | Excel Discussion (Misc queries) | |||
Masking numbers | Excel Worksheet Functions |