(VBA Excel) Extract Text and related Letter from String and output result

Question

So the scenario I have is there are letter with a number:

Desired Output after Program (Note an underscore is visually used but I need a space:

____________F
__G
_____E
__G__E______F

Currently I have written code in the Number and First Letter Column to extract the number and first letter:

First Letter:

LEFT(A2,1)

Number:

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Now my VBA Script can take the number and character to get the information and output for (For ONE Error Code):

Private Sub Code_Printer_Click()

Dim myFile As String, rng As Range, cellValue As Variant, I As Integer, j As Integer

myFile = "C:\Reformatted.txt"
Set rng = Selection

Open myFile For Output As #1

For I = 1 To rng.Rows.Count
    For j = 1 To rng.Columns.Count

If j = rng.Columns.Count Then
    cellValue = Space(rng.Cells(I, 1)) + CStr(rng.Cells(I, 2).Value)
    Print #1, cellValue
End If

    Next j
    cellValue = ""
Next I

Close #1
Shell "C:\Windows\Notepad.exe C:\Reformatted.txt", 1

End Sub

RESULT:

So please help me process multiple codes in the same event.

If an excel function needs to be done thats fine. If its easier to extract the number in VBA thats ok to. Non technical people will use so the more VBA the better. Please let me know if this is a slow or if there is a faster, easier way to do this! :)

-----------------------------Final Data---------------------------------------

Answer 1

This is how I'd do it -

Sub test()
Dim myFile As String
myFile = "C:\reformatted.txt"
Open myFile For Output As #1
Dim iPar As Integer
Dim sChar As String
Dim sBlank As Long
Dim cont As Boolean

Dim mystring As String

For Each c In Range("A:A")
If c <> "" Then
'Get first
        iPar = InStr(1, c, "(")
        If Mid(c, iPar - 1, 1) = "" Then
            If Mid(c, iPar - 2, 1) = "" Then
            sChar = Mid(c, iPar - 3, 1)
            Else: sChar = Mid(c, iPar - 2, 1)
            End If
        Else: sChar = Mid(c, iPar - 1, 1)
        End If
        If IsNumeric(Mid(c, iPar + 1, 2)) Then
            sBlank = Mid(c, iPar + 1, 2)
        Else: sBlank = Mid(c, iPar + 1, 1)
        End If
        mystring = Space(sBlank) & sChar
        cont = InStr(iPar + 1, c, "(")

    Do While cont = True

        iPar = InStr(iPar + 1, c, "(")
        If Mid(c, iPar - 1, 1) = "" Then
            If Mid(c, iPar - 2, 1) = "" Then
            sChar = Mid(c, iPar - 3, 1)
            Else: sChar = Mid(c, iPar - 2, 1)
            End If
        Else: sChar = Mid(c, iPar - 1, 1)
        End If
        If IsNumeric(Mid(c, iPar + 1, 2)) Then
            sBlank = Mid(c, iPar + 1, 2)
        Else: sBlank = Mid(c, iPar + 1, 1)
        End If

        If sBlank + 1 > Len(mystring) Then
            mystring = mystring & Space(sBlank - Len(mystring)) & sChar
        Else: mystring = Application.WorksheetFunction.Replace(mystring, sBlank + 1, 1, sChar)
        End If
        cont = InStr(iPar + 1, c, "(")

    Loop

       Print #1, mystring
     Else: Exit For
   End If
Next
Close #1
Shell "C:\Windows\Notepad.exe C:\reformatted.txt", 1

End Sub

So it searches for the first parentheses ( and tackles the character and spaces. Then it looks for another ( - if it finds one, it continues, otherwise it's done (and prints).

If it continues, it finds the next ( and does the same test finding the character and spaces, then checks the string to see if the length of the string is greater than the number of spaces. If it is, it replaces a blank character with your character. If not, it appends spaces and then inserts the character at the end.

Then it searches again for a ( to repeat the process.

Right now it is searching column A and printing in column B - adjust as needed. You can print mystring to file.

Answer 2

This is not a complete answer, but for the 2nd point of your pseudo-code, you can use the wildcard # to search a string for any numeric characters. More information is here MSDN.