Parsing excel field help !

  • Thread starter Thread starter luis.a.roman
  • Start date Start date
L

luis.a.roman

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
 
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
 
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
 
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
 
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,100,10,1})

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

XL2002, WinXP
 
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
Click to expand...

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.
 
Back
Top