Concatenation Formula needed

[Gaffnr]

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.

 

[chelsea]

try:
=A1&REPT(" ",34-LEN(A1))&B1

HTH

Regards
Sebation.G

 

[papou]

Hello Gaffnr
Lets have a try:
=IF(LEN(A1)<33,A1&REPT(" "33-LEN(A1))&B1,MID(A1,1,33)&B1)

HTH
Cordially
Pascal

 

[Rick Rothstein \(MVP - VB\)]

This should work for you in all situations...

=LEFT(A1&REPT(" ",32),32)&" "&B1

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Gaffnr]

to all of you that replied - thats wonderful - thanks so much