Fixed field width in cells

[apv98]

Hi,

I am very new to this forum. I have two queries:

1. I have got data in various columns in a work sheet. Each column
contains data from a table(outside excel). I want to copy all the data
in a row to one cell. This I acheived through the formula = A1 & B1---
etc. However I want to maintain the field length of each too. For
example the first field in my excel file represents a field which is 20
characters long and the next 10 characters long. The data for the first
field is abcd and for the second is 1234.

What I want in the cell is ABCD (16 character spacingin between)1234

Is it possible in excel to do this?

Thanks & regards,
Paul

 

[Guest]

apv98,

One way to do this is as follows:

A1 & rept(" ",20-len(A1)) & B1 & rept(" ",10-len(B1)) & ...

It gets long, and if you have too many fields you could run up against the limit for the number of characters in a formula which I believe is 255.

Art

 

[apv98]

Hi,

Thanks for your reply. However I am not able to use this formula as th
number of columns on which I want to do this extends to AE.

Looking forward to your help excel gurus.

Thanks & regards,
Pau