Will write spaces but not the last character (VBA Excel) -

data:

desired output:

current output:

my current code:

private sub generateflatfile_click() dim myfile string, rng range, cellvalue variant, integer, j integer, spacingcode string  dim ipar integer dim sblank long dim cont boolean dim mystring string  myfile = "c:\reformatted.txt" set rng = selection  open myfile output #1  dim strarr(1 63) string, intbeg integer, intend integer, intcount integer, schar string  = 2 rng.rows.count     j = 1 rng.columns.count         if instr(1, cstr(cells(1, j).value), "63") = 1             strarr(val(cells(1, j).value)) = cells(i, j).value         elseif instr(1, cstr(cells(1, j).value), "code")                  ipar = instr(1, cstr(cells(i, j).value), "(")                 if mid(cells(i, j).value, ipar - 1, 1) = ""                     if mid(cells(i, j).value, ipar - 2, 1) = ""                     schar = mid(cells(i, j).value, ipar - 3, 1)                     else: schar = mid(cells(i, j).value, ipar - 4, 1)                     end if                 else: schar = mid(cells(i, j).value, ipar - 2, 1)                 end if                 if isnumeric(mid(cells(i, j).value, ipar + 1, 2))                     sblank = mid(cells(i, j).value, ipar + 1, 2)                 else: sblank = mid(cells(i, j).value, ipar + 1, 1)                 end if                 mystring = space(sblank) & schar                 cont = instr(ipar + 1, cstr(cells(i, j).value), "(")              while cont = true                  ipar = instr(ipar + 1, cstr(cells(i, j).value), "(")                 if mid(cells(i, j).value, ipar - 1, 1) = ""                     if mid(cells(i, j).value, ipar - 2, 1) = ""                     schar = mid(cells(i, j).value, ipar - 3, 1)                     else: schar = mid(cells(i, j).value, ipar - 2, 1)                     end if                 else: schar = mid(cells(i, j).value, ipar - 1, 1)                 end if                 if isnumeric(mid(cells(i, j).value, ipar + 1, 2))                     sblank = mid(cells(i, j).value, ipar + 1, 2)                 else: sblank = mid(cells(i, j).value, ipar + 1, 1)                 end if                  if sblank + 1 > len(mystring)                     mystring = mystring & space(sblank - len(mystring)) & schar                 else: mystring = application.worksheetfunction.replace(mystring, sblank + 1, 1, schar)                 end if                 cont = instr(ipar + 1, cstr(cells(1, j).value), "(")              loop          elseif instr(1, cstr(cells(1, j).value), "difference")             spacingcode = space(rng.cells(i, j))         else         intbeg = val(left(cells(1, j).value, instr(1, cells(1, j).value, "-") - 1))         intend = val(right(cells(1, j).value, len(cells(1, j).value) - instr(1, cells(1, j).value, "-")))         intcount = 1         t = intbeg intend             strarr(t) = mid(cells(i, j).value, intcount, 1)             intcount = intcount + 1         next t         end if     next j     t = 1 ubound(strarr)         if strarr(t) = "" strarr(t) = " "         cellvalue = cellvalue + strarr(t)     next t     erase strarr     cellvalue = cellvalue + spacingcode     cellvalue = cellvalue + mystring     print #1, cellvalue     cellvalue = "" next  close #1 shell "c:\windows\notepad.exe c:\reformatted.txt", 1 end sub 

i've been trying awhile when there 2 spaces between ( , letter doesnt seem work.

f , g works since there 1 space. when there multiple letter codes or 2 spaces doesn't work. time!

it seems problem merely last column. here udf, using regular expression will

• search string
• look "word" (sequence of letters, digits, and/or underscores) followed 0 or more spaces , open parentheses mark (
• combine word sequences space separated string

you should able incorporate code.

if provide more detail possible types of codes, regex might altered, above seems fit.

=================================================

function codes(s string) string     dim re object, mc object, m object  set re = createobject("vbscript.regexp") re     .global = true     .pattern = "\b(\w+)\s*\("     if .test(s) = true         set mc = re.execute(s)         each m in mc             codes = codes & space(1) & m.submatches(0)         next m     end if end codes = mid(codes, 2) end function 

=================================================