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

[HaakLord]

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!

Thank you in advance!

 

[godsaaint]

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

 

[HaakLord]

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]

Here are 2 methods:

	A	B	C
1	abcd1234	0000123	0000123
2	1290ab9	1290009	1290009
3	12ab	1200000	1200000
4	987ab65	9870065	9870065
5	1234567	1234567	1234567
6	A72I	0720000	0720000
7	5623YO2	5623002	5623002
8	4321	4321000	4321000
9	51342	5134200	5134200
10	RKDR241	0000241	0000241

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet12​

Worksheet Formulas Cell Formula 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B1 {=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:7")),1)+0,0))} <thead> </thead><tbody> </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]

Here's a UDF you can use. An example of its use is below:

Excel Workbook
	A	B
1	ab345F0	0034500
2	1RT9Z	1009000
3	0DEF020	0000020
4	1A4Z	1040000
5	A72I	0720000
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.

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]

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]

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]

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.

Glad we could help!

 

[HaakLord]

Very clever! Thanks for the explanation!