# How to insert "0" where alphabet letters are missing.

#### itr0754

##### New Member
Hi everyone !! I have been working on a macro to evaluate the contents of multiple cells in a spreadsheet and fill in the missing letters of the alphabet with "0". In short, I need to complete the contents of the cell to show 7 characters which will be either seven "0", the letters A to G, or a combination of both.

For example, if my cell shows "BC", I need to insert "0" to replace the missing letters (A to G) resulting in "0BC0000". Or, if my cell shows "AEF", I need to insert "0" to make it "A000EF0". If no letters are present in the cell (blank cell), I should insert "0000000". On the other hand, if the cell shows "ABCDEFG", nothing needs to be inserted.

Does this make sense to you guys ?? I would really appreciate your comments and help. I am kind of stumped and doing it manually is not an option; spreadsheet holds over 1000 cells.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Assuming your values are in Column A, give this macro a try...
Code:
``````[table="width: 500"]
[tr]
[td]Sub FillMissingLettersWithZeroes()
Dim R As Long, Zeros As String, V As Variant, Data As Variant, Letters() As String
Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For R = 1 To UBound(Data)
Zeros = "0000000"
Letters = Split(StrConv(Data(R, 1), vbUnicode), Chr(0))
For Each V In Letters
If Len(V) Then Mid(Zeros, Asc(V) - 64) = V
Next
Data(R, 1) = Format\$(Zeros, "'@@@@@@@")
Next
Range("[B][COLOR="#FF0000"]A1[/COLOR][/B]").Resize(UBound(Data)) = Data
End Sub[/td]
[/tr]
[/table]``````
The above code overwrites the existing data with the fixed data. If you do not want this, change the red A1 to a starting cell address where the output should be placed.

Last edited:
Change "G" to the last letter you want to check. Put your data in A1 and down, the result will be in B1

Code:
``````Sub Insert_Zeros()
Dim c As Range, n As Long, s As String, l As String
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
s = ""
For n = 1 To Columns("G").Column
l = Replace(Cells(1, n).Address(False, False), "1", "")
If InStr(c.Value, l) > 0 Then s = s & l Else s = s & "0"
Next
c.Offset(0, 1).Value = s
Next
End Sub``````

Thanks so much guys !! DanteAmor's sub worked except for 2 details. First, if the cell is empty, instead of seven 0, it only inserts one 0. Also, if the value is a single "E", it inserts "0.00E+00".

Here's what it looks like, column on the left is A (original values) and column on the right is B (processed values). They all work except for "E" and an empty cell.

 ABG AB0000G AG A00000G CDG 00CD00G EF 0000EF0 E 0.00E+00 F 00000F0 AEF A000EF0 A A000000 B 0B00000 C 00C0000 D 000D000 F 00000F0 G 000000G ABCDEFG ABCDEFG 0 AE A000E00

<colgroup><col><col></colgroup><tbody>
</tbody>

Thanks so much guys !! DanteAmor's sub worked except for 2 details. First, if the cell is empty, instead of seven 0, it only inserts one 0. Also, if the value is a single "E", it inserts "0.00E+00".
Did you try the code I posted (it does not have the above problems)?

I just did and was able to get the result I needed. Thank you very much !! Both methods actually worked.

You're welcome and thanks for the feedback.

Replies
0
Views
963
Replies
2
Views
235
Replies
11
Views
822
Replies
7
Views
302
Replies
0
Views
357

1,203,261
Messages
6,054,424
Members
444,724
Latest member

### 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