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.

[TABLE="width: 125"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]ABG[/TD]
[TD] AB0000G[/TD]
[/TR]
[TR]
[TD]AG[/TD]
[TD] A00000G[/TD]
[/TR]
[TR]
[TD]CDG[/TD]
[TD] 00CD00G[/TD]
[/TR]
[TR]
[TD]EF[/TD]
[TD] 0000EF0[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"] 0.00E+00[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD] 00000F0[/TD]
[/TR]
[TR]
[TD]AEF[/TD]
[TD] A000EF0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] A000000[/TD]
[/TR]
[TR]
[TD]B [/TD]
[TD] 0B00000[/TD]
[/TR]
[TR]
[TD]C [/TD]
[TD] 00C0000[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD] 000D000[/TD]
[/TR]
[TR]
[TD]F [/TD]
[TD] 00000F0[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD] 000000G[/TD]
[/TR]
[TR]
[TD]ABCDEFG[/TD]
[TD] ABCDEFG[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD] A000E00[/TD]
[/TR]
</tbody>[/TABLE]

 

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