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

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

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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160414

Microsoft Office Help