How to add character after last word written on line

Question

Dear community,

I have a doubt that I’ve tried everything, but I can’t solve.

I have a list of former names;

José Mario da Silva
Mariana Cristina
William Caio
Jorge Luiz Campos de Silva Andrade

In case there are more than 2000 names... what I have to do is after the last word typed I have to put 50 empty " " character and after the last empty insert 15 more "00000000000" character... to insert I was using the following formula ex:

=CONCATENAR(A1;"                        ";"000000000000000")

My big problem is that the lines are getting misaligned, because each name has an amount of character... in case it should look like this:

José Mario da Silva                      000000000000000
Mariana Cristina                         000000000000000
William Caio                             000000000000000
Jorge Luiz Campos de Silva Andrade       000000000000000

But it’s staying that way:

José Mario da Silva                      000000000000000
Mariana Cristina                     000000000000000
William Caio                   00000000000000
Jorge Luiz Campos de Silva Andrade                   000000000000000

Someone could help me in this case, because I don’t know but what to do.

Answer 1

Check which is the longest name length you have in your table and replace the number 50 of that formula with.

A1 is the cell where the names are written, in my example:

=CONCATENAR(A1;REPT(" ";50-NÚM.CARACT(A1));"!00000000000000")

In excel with this formula it does not look right, but if I copy the contents of the cells and paste in text editor with characters of variable size is right.

Answer 2

I tried each of the above codes, they worked but still, I don’t know why when I inserted in txt they were misaligned.

After a certain time smashing the head and with the help of programmer Anderson, we arrived at this code that meets all specifications;

Option Explicit

Sub Macro2()

Dim iArq As Long
Dim NOME As String
Dim op As Variant
Dim linha As Long
Dim resposta As String
   
linha = 1
While ThisWorkbook.Sheets("Concluidos").Cells(linha, 1) <> ""

NOME = ThisWorkbook.Sheets("Concluidos").Cells(linha, 1)
      
While Len(NOME) < 50
           
NOME = NOME + " "
            
Wend
NOME = NOME & "000000000000000"
       
iArq = FreeFile

Open fileELEB For Append As iArq

Print #iArq, NOME

Close #iArq
linha = linha + 1
Wend
    
MsgBox "O arquivo foi exportado com sucesso! ", vbInformation, "Exportar arquivos"

resposta = MsgBox("Você gostaria voltar ao menu?", vbYesNo + vbQuestion, "QNIS - CAIXA")
    
If resposta = vbYes Then

    
Else

Workbooks("QNIS V3.xlsm").Close False
Unload FormQNIS
End If

End Sub

I want to thank you in advance for all your help, thank you very much!!!

Answer 3

Excel Formula

With extra reference cell

Insert this formula into a cell =MÁXIMO((NÚM.CARACT($A:$A))), for example Z1 and tighten Ctrl+Shift+Enter (remember to select each cell and press the buttons after inserting matrices in formulas, for example A:A)

And use this formula: =A1&REPT("*";($Z$1-NÚM.CARACT(A1))+50)&"000000000000000"

No extra reference cell

Or without the Z1 =A1&REPT("*";(MÁXIMO((NÚM.CARACT($A$1:$A$2000)))-NÚM.CARACT(A1))+50)&"000000000000000" However, I suggest using the previous formula, because you only need to press Ctrl+Shift+Enter in the reference cell Z1

This is the result, by changing the font, for a single-spaced font is possible to view, I changed to * to see better. In tests only 15 asterisks are placed

Excel VBA

Insert formula and Autofill by VBA

Assuming the data in column A and the new data in B, enter the desired formula in B1 and run this code for Autofill in B.

lrA = Planilha1.Range("A" & Rows.Count).End(xlUp).Row 
Planilha1.Range("B1").AutoFill Destination:=Planilha1.Range("B1:B" & lrA)

VBA

I was going to make a code, but I didn’t even do it because with Excel formula the problem has already been solved. However, if you want, the same logic is done in VBA. Can be used the function Len and loops. And to write a . txt, this link