# Ideas for a find and replace function to replace all letters with numbers

#### HaakLord

##### New Member
Hi all,
I need a function that will replace all letters in a given cell with the number "0" and if the string of digits is shorter than seven digits then 0's need to be added to get it up to 7 digits. Basically, invoices are received that need to have a seven digit invoice number but they are often received with between 4-7 characters that are alpha-numeric. It would save me loads of time to not have to change these invoice numbers individually!

I was thinking of simply using a find and replace function but I'm not sure how I could FIND all alphabetical characters other than but nesting twenty seven different FIND functions, which isn't exactly efficient.

Let me know if you need any more info or have any other questions!

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### godsaaint

##### Active Member
There a few ways to tackle this, but to be sure. could you provide a few examples of these invoices?

#### HaakLord

##### New Member
Here are some examples of how they could come, and how I would like them to be.

A72I - 0720000
5623YO2 - 5623002
4321 - 4321000
51342 - 5134200
RKDR241 - 0000241

#### Eric W

##### MrExcel MVP
Here are 2 methods:

ABC
1abcd123400001230000123
21290ab912900091290009
312ab12000001200000
4987ab6598700659870065
5123456712345671234567
6A72I07200000720000
75623YO256230025623002
8432143210004321000
95134251342005134200
10RKDR24100002410000241

</tbody>
Sheet12

Worksheet Formulas
CellFormula
C1=IFERROR(MID(A1,1,1)+0,0)&IFERROR(MID(A1,2,1)+0,0)&IFERROR(MID(A1,3,1)+0,0)&IFERROR(MID(A1,4,1)+0,0)&IFERROR(MID(A1,5,1)+0,0)&IFERROR(MID(A1,6,1)+0,0)&IFERROR(MID(A1,7,1)+0,0)

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B1{=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:7")),1)+0,0))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If you have the new TEXTJOIN function, use the B1 formula with Control+Shift+Enter. If you have an older version of Excel, you can expand it like in C1, no CSE.

#### JoeMo

##### MrExcel MVP
Here's a UDF you can use. An example of its use is below:
Excel Workbook
AB
1ab345F00034500
21RT9Z1009000
30DEF0200000020
41A4Z1040000
5A72I0720000
Sheet1

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Enter the function in your worksheet just like a worksheet function (see example above)
7. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
``````Function LettersToZeros(S As String) As String
Dim i As Long
If S = "" Then
LettersToZeros = ""
Exit Function
End If
For i = 1 To Len(S)
If Mid(S, i, 1) Like "[A-Za-z]" Then
S = Replace(S, Mid(S, i, 1), "0")
End If
Next i
If Len(S) < 7 Then S = S & String(7 - Len(S), "0")
LettersToZeros = S
End Function``````

#### Fennek

##### Active Member
Hi,

here is a code to start with: it replace any letter to the same amount of "0". You have to adjust a loop over your input range.

Sub No_letter()
'link to MS vb Regular Expressen 5.5
Dim Reg As RegExp
Set Reg = CreateObject("vbscript.regexp")
Tx = Cells(1, 1)
Reg.Global = True
Reg.Pattern = "([A-Za-z]+)"
If Reg.Test(Tx) Then
For Each R In Reg.Execute(Tx)
Debug.Print R.Length
iRep = WorksheetFunction.Rept(0, R.Length)
For Each SM In R.SubMatches
Debug.Print SM
Tx = Reg.Replace(Tx, iRep)
Next SM
Next R
End If
Debug.Print Tx
End Sub

regards

#### HaakLord

##### New Member
Thanks to Joe and Eric those work perfect. I ended up using the IFERROR function for simplicity's sake. Could you explain how this function works? I don't quite understand why an IFERROR is used. I see that the MID will return whatever character is return from that location, not sure what the +0 does, and I see that 0 is the value returned by the IFERROR function for when an error is found. What error is the function finding?

#### Eric W

##### MrExcel MVP
The IFERROR can actually pick up 2 potential errors. As you noted, the MID will pick up the nth character of the input string. If it is numeric, adding the 0 to it (+0) will coerce the sting version of a number to an actual number. Adding 0 to a letter will cause an error. This will trigger the IFERROR and you'll get the 0 appended to the output string. The other potential error is if you try to get the 7th byte of a 4-character string. MID will return a null, and the +0 will cause the error again, which works to append the zeros to the end.

#### HaakLord

##### New Member
Very clever! Thanks for the explanation!

Replies
2
Views
84
Replies
9
Views
264
Replies
4
Views
436
Replies
3
Views
419
Replies
4
Views
331

1,191,204
Messages
5,985,270
Members
439,953
Latest member
suchitha

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back