Does any know how can I check all the characters of a field and check
that there is not an alphanumeric character (a-z) and if there is one
changing to zero.

Ex. 5J00 = would like to change it to 5000.
20A5 = would like to change it to 2005.

Thank you.

Luis

try

Sub changecharactertozero()
For Each c In Selection
For i = 1 To Len(c)
If Not IsNumeric(Mid(c, i, 1)) Then c.Replace Mid(c, i, 1), "0"
Next i
Next c
End Sub

--
Don Guillett
SalesAid Software

wrote in message
ps.com...
Does any know how can I check all the characters of a field and check
that there is not an alphanumeric character (a-z) and if there is one
changing to zero.

Ex. 5J00 = would like to change it to 5000.
20A5 = would like to change it to 2005.

Thank you.

Luis

If the test cell will ALWAYS be 4 characters,
this formula works:

With
A1: (4 character value)

B1:
=IF(ISERR(--MID(A1,1,1)),"0",MID(A1,1,1))&IF(ISERR(--MID(A1,2,1)),"0",MID(A1,2,1))&IF(ISERR(--MID(A1,3,1)),"0",MID(A1,3,1))&IF(ISERR(--MID(A1,4,1)),"0",MID(A1,4,1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Does any know how can I check all the characters of a field and check
that there is not an alphanumeric character (a-z) and if there is one
changing to zero.

Ex. 5J00 = would like to change it to 5000.
20A5 = would like to change it to 2005.

Thank you.

Luis

Try this:

=SUBSTITUTE(A1,MID(A1,MATCH(FALSE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1),0)

ctrl+shift+enter, not just enter



" wrote:

Does any know how can I check all the characters of a field and check
that there is not an alphanumeric character (a-z) and if there is one
changing to zero.

Ex. 5J00 = would like to change it to 5000.
20A5 = would like to change it to 2005.

Thank you.

Luis

Caveat.....works if there is only one non-numeric or, if more than one, they
are the same character.

Works for 5AA5
Fails for 5AB5

***********
Regards,
Ron

XL2002, WinXP


"Teethless mama" wrote:

Try this:

=SUBSTITUTE(A1,MID(A1,MATCH(FALSE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1),0)

ctrl+shift+enter, not just enter



" wrote:

Does any know how can I check all the characters of a field and check
that there is not an alphanumeric character (a-z) and if there is one
changing to zero.

Ex. 5J00 = would like to change it to 5000.
20A5 = would like to change it to 2005.

Thank you.

Luis

Here's a shorter alternative:

With
A1: (the test value)

=SUMPRODUCT(IF(ISNUMBER(--MID(A1,{1,2,3,4},1)),MID(A1,{1,2,3,4},1))*{1000,10 0,10,1})

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

If the test cell will ALWAYS be 4 characters,
this formula works:

With
A1: (4 character value)

B1:
=IF(ISERR(--MID(A1,1,1)),"0",MID(A1,1,1))&IF(ISERR(--MID(A1,2,1)),"0",MID(A1,2,1))&IF(ISERR(--MID(A1,3,1)),"0",MID(A1,3,1))&IF(ISERR(--MID(A1,4,1)),"0",MID(A1,4,1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Does any know how can I check all the characters of a field and check
that there is not an alphanumeric character (a-z) and if there is one
changing to zero.

Ex. 5J00 = would like to change it to 5000.
20A5 = would like to change it to 2005.

Thank you.

Luis

skrev i en meddelelse
ps.com...
Does any know how can I check all the characters of a field and check
that there is not an alphanumeric character (a-z) and if there is one
changing to zero.

Ex. 5J00 = would like to change it to 5000.
20A5 = would like to change it to 2005.

Thank you.

Luis


Luis

Here's a generic array formula. It will work for
an arbitrary number of characters in the cell and
for an arbitrary number of non-numeric characters.

=SUM(IF(ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))) ,1)+0),0,
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))))

To be confirmed with <Shift<Ctrl<Enter, also if edited later.

--
Best regards
Leo Heuser

Followup to newsgroup only please.