Substitute help I think

[Celticshadow]

Hi All

May I ask the board if they could peruse the below and perhaps help me with sorting out a formula as per th instructions contained within. Many thanks in advance.

Kind Regards

PS Apologies I do not seem to have got a handle on this html editor thing yet.doh

[RANGE=cls:xl2bb-100][XR][XH=cs:9]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][/XR][XR][XH]1[/XH][XD=h:c]DATA[/XD][XD=h:c]REQUIRED RESULT[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]2[/XH][XD=h:c]579[/XD][XD=h:c]000[/XD][XD=h:l|fw:b|c:ff0000]HI CHAPS - I WOULD LIKE TO REPLACE ANY LETTER IN THE SEQUENCE OF DATA IN COLUMN A WITH A ZERO AND ANY FIGURES OF 5 AND ABOVE WITH A ZERO AND THEN PLACE THE RESULT IN COLUMN B. ANY FIG OF 4 DOWN TO ZERO TO BE PLACED IN COLUMN B AS IS. MANY THANKS[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]3[/XH][XD=h:c]079[/XD][XD=h:c]000[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]4[/XH][XD=h:c]534[/XD][XD=h:c]034[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]5[/XH][XD=h:c]255[/XD][XD=h:c]200[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]6[/XH][XD=h:c]123[/XD][XD=h:c]123[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]7[/XH][XD=h:c]132[/XD][XD=h:c]132[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]8[/XH][XD=h:c]111[/XD][XD=h:c]111[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]9[/XH][XD=h:c]513[/XD][XD=h:c]013[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]10[/XH][XD=h:c]F49[/XD][XD=h:c]040[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]11[/XH][XD=h:c]322[/XD][XD=h:c]322[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]12[/XH][XD=h:c]101[/XD][XD=h:c]101[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]13[/XH][XD=h:c]045[/XD][XD=h:c]040[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]14[/XH][XD=h:c]11B[/XD][XD=h:c]110[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]15[/XH][XD=h:c]24d[/XD][XD=h:c]240[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]16[/XH][XD=h:c]21L[/XD][XD=h:c]210[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]17[/XH][XD=h:c]43P[/XD][XD=h:c]430[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]18[/XH][XD=h:c]S31[/XD][XD=h:c]031[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]19[/XH][XD=h:c]U3U[/XD][XD=h:c]030[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH=cs:9][RANGE][XR][XD]...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[shg]

Brute force, I reckon:

=IF(CODE(LEFT(A2)) > 52, 0, LEFT(A2))
& IF(CODE(MID(A2,2,1)) > 52, 0, MID(A2,2,1))
& IF(CODE(RIGHT(A2)) > 52, 0, RIGHT(A2))

 

[machopicho]

Try:

Sub test()
For r = 1 To Cells(Rows.Count, 1).End(xlUp).Row
n = Len(Cells(r, 1))
For i = 1 To n
If Mid(Cells(r, 1), i, 1) >= 5 And Mid(Cells(r, 1), i, 1) <= 9 Or _
Asc(Mid(Cells(r, 1), i, 1)) >= 65 And Asc(Mid(Cells(r, 1), i, 1)) <= 90 Or _
Asc(Mid(Cells(r, 1), i, 1)) >= 97 And Asc(Mid(Cells(r, 1), i, 1)) <= 122 Then
Cells(r, 2) = Replace(Cells(r, 1), Mid(Cells(r, 1), i, 1), "0")
End If
Next
If Cells(r, 2) = "" Then
Cells(r, 2) = Cells(r, 1)
End If
Next
End Sub