Cell Padding & Concatenate

[filky]

I am trying to convert a csv to a fixed length format using excel. Fo
example, if I have

22252,E,LAB01001
22253,E,LAB01002

and I use contatenate I can create a single cell containing this

22252 E LAB01001
22253 E LAB01002

However, if I have

225,E,LAB01001
22256,ER,LAB01001

and I know how long each field should be how do I consistently get

225 E LAB01001
22256 ER LAB01001

I wondered whether I could have a row of required cell lengths abov
the data and then concatenate the cells to a fixed length based on th
number entered but can't find a way of doing it. For example

14,7,8
225,E,LAB01001
22256,ER,LAB01001

I hope this is clear. Can anyone help

 

[Andy B]

Hi

You could try something like:
=REPT(" ",14-LEN(B10))&B10&REPT(" ",7-LEN(C10))&C10&REPT(" ",8-LEN(D10))&D10
This will concatenate B10, C10 and D10, after padding them to be 14, 7 and 8
respectively.

 

[Jacques Brun]

If you know the maximum length of each field you can use
something like :
=Concatenate(Left(A1&" ",10),
Left(B1&" ",6),
Left(C1&" ",8))

You will create a fixed length record with left justified
fields of respectively 10, 6 and 8 characters length

14,7,8 ==> 14 7 8
225,E,LAB01001 ==> 225 E LABO1001
22256,ER,LAB01001 ==> 22256 ER LABO1001

Depending on your requirements you can also consider
using something like =right("000000000"&A1,9)
to convert a variable length numeric string into a
9 (for example) characters long number :

627 ==> 000000627
9876 ==> 000009876

Hope this helps...

 

[filky]

Excellent. Thanks guys. Now I look at your solutions they look quit
simple, but I guess that's always the case once you know :-