Concatenating two variable fields into a static length text field
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. |
Concatenating two variable fields into a static length text field
=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. |
Concatenating two variable fields into a static length text fi
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. |
All times are GMT +1. The time now is 04:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com