I have a spreadsheet with two columns of variable length text that I want to
concatenate into a static length text field. The first column can be
anywhere from 1 to 28 characters and the second is only 2 characters. How do
I concatenate the field and space fill any columns so that the net result is
a column with a static 30 characters and the second column is always in
position 29 and 30? Here is a sample of the data:

Column 1 Column 2 Desired result
A805605252 01 A805605252 01
2009/32-1980 02 2009/32-1980 02
2009/163-1980 01 2009/163-1980 01
2009/167-1980 03 2009/167-1980 03
2009/302-1980 01 2009/302-1980 01
2009/303-1980 01 2009/303-1980 01
259-96-21 06 259-96-21 06

If you use concatenate, the function trims the spaces at the end. It can be
done with performing a length check and adding the appropriate number of
spaces, but a separate else if needs to be created for each length. This can
be cumbersome. I know I can save the file in a formated text space delimited
(.prn) and re-import it to get the result, but would rather do so in a
function.

Any help would be appreciated.

=LEFT(A1&REPT(" ",28),28)&B1
and fill down

"Young Programmer Lad" wrote
in message ...
I have a spreadsheet with two columns of variable length text that I want
to
concatenate into a static length text field. The first column can be
anywhere from 1 to 28 characters and the second is only 2 characters. How
do
I concatenate the field and space fill any columns so that the net result
is
a column with a static 30 characters and the second column is always in
position 29 and 30? Here is a sample of the data:

Column 1 Column 2 Desired result
A805605252 01 A805605252 01
2009/32-1980 02 2009/32-1980 02
2009/163-1980 01 2009/163-1980 01
2009/167-1980 03 2009/167-1980 03
2009/302-1980 01 2009/302-1980 01
2009/303-1980 01 2009/303-1980 01
259-96-21 06 259-96-21 06

If you use concatenate, the function trims the spaces at the end. It can
be
done with performing a length check and adding the appropriate number of
spaces, but a separate else if needs to be created for each length. This
can
be cumbersome. I know I can save the file in a formated text space
delimited
(.prn) and re-import it to get the result, but would rather do so in a
function.

Any help would be appreciated.

Works like a charm. Thanks!

"Bob Umlas" wrote:

=LEFT(A1&REPT(" ",28),28)&B1
and fill down

"Young Programmer Lad" wrote
in message ...
I have a spreadsheet with two columns of variable length text that I want
to
concatenate into a static length text field. The first column can be
anywhere from 1 to 28 characters and the second is only 2 characters. How
do
I concatenate the field and space fill any columns so that the net result
is
a column with a static 30 characters and the second column is always in
position 29 and 30? Here is a sample of the data:

Column 1 Column 2 Desired result
A805605252 01 A805605252 01
2009/32-1980 02 2009/32-1980 02
2009/163-1980 01 2009/163-1980 01
2009/167-1980 03 2009/167-1980 03
2009/302-1980 01 2009/302-1980 01
2009/303-1980 01 2009/303-1980 01
259-96-21 06 259-96-21 06

If you use concatenate, the function trims the spaces at the end. It can
be
done with performing a length check and adding the appropriate number of
spaces, but a separate else if needs to be created for each length. This
can
be cumbersome. I know I can save the file in a formated text space
delimited
(.prn) and re-import it to get the result, but would rather do so in a
function.

Any help would be appreciated.