Sorry, I misread your posting to say you always wanted a separating space in
front of B1. Try this instead...
=LEFT(A1&REPT(" ",33),33)&B1
It will do what you asked for and, as a bonus, is shorter than my original
formula.
Rick
"Rick Rothstein (MVP -
VB)" wrote in
message ...
This should work for you in all situations...
=LEFT(A1&REPT(" ",32),32)&" "&B1
Rick
"Gaffnr" wrote in message
...
I am trying to joins 2 text fields in columns a & b.
Column A is a variable field of differring lengths and characters in
every
row.
Column B is the same string in every row of 7 characters long.
I need to join A&B where B starts always at position 34 in the string but
with blank spaces between them where column A finishes before position
34.
this means the total concatenated field length will be 40 which i need to
export to another system.
So, if column A was the alphabet A-Z i'd want :
A-Z (posistions 1 - 26)
7 spaces (positions 27 - 33)
Column B (positions 34 - 40).
(NB if column A content was 38 long, id want to overwrite the contents
with
column B from position 34.
Remember, field length must always be 40, no more, no less.
Ive spent hours trying to do this and thought I was profficient at
Excel -
it seems not. To confirm, its Excel 2003.
Thanks to any would be genius's out there that can help a frustrated
soul.