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

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

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

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

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

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

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

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

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

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