Masking out characters in a cell

[dnardi]

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?

 

[Rick Rothstein \(MVP - VB\)]

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

 

[ryguy7272]

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

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

Which function should be used to either mask out the unwanted letters and

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&"0123456789")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1))))

Rick

 

[T. Valko]

That's susceptible to row insertions.

ROW($1:$999)

So, it's a trade-off making a particular suggestion.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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!

 

[Rick Rothstein \(MVP - VB\)]

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&"0123456789")),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