Y
Young Programmer Lad
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.
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.