Characters same length in column

[ktuprah]

Thank You so much for your advice. The problem is the data comes into excel
from a different program and then has to be exported to notepad. So lets
say the account# column is supposed to have 10 characters and a cell only
has 7. Then when it goes to notepad it would have original 7 characters and
3 spaces. Would you be able to look at the following code and give some
advice(the output has been left as comments as they are in theory)?



Private Sub Worksheet_Change(ByVal Target As Range)

Const MaxLen As Long = 10

If Target.Column = 4 And Len(Target.Value) < MaxLen Then

MsgBox "Entry too short"

Application.EnableEvents = False

dif = MaxLen - Len(Target.Value)

result = Rep(" ", difference) & Target.Value



Dim Check, Counter

Check = True: Counter = 0



Do While Counter < dif

Counter = Counter + 1

Spaces = spaces & " "

Loop



Spaces2='Target.Value = Replace(Target.Value, 1 - difference, difference, "
")



'Target.Value = CONCATENATE(REPT(" ",difference),Target.Value)

'Target.Value = spaces & Target.Value

'Target.Value = spaces2 & Target.Value



Application.EnableEvents = True

End Sub

 

[joel]

I wouldn't make the changes as part of the data entry. I would add th
extra spaces when the file is saved as a text file. I can esily write
macro which would save the file and make the corrections when the fil
gets saved.

Notepad is just a tool used to read text data. It doesn't have to ge
copied and pasted into excel. You just need to save the file as tex
with an TXT extension