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

[itr0754]

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.

 

[Rick Rothstein]

Assuming your values are in Column A, give this macro a try...

[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.

 

[DanteAmor]

What about the following.
Change "G" to the last letter you want to check. Put your data in A1 and down, the result will be in B1

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

 

[itr0754]

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>

 

[Rick Rothstein]

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)?

 

[itr0754]

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

 

[DanteAmor]

You're welcome and thanks for the feedback.