Concatenate cells but add blanks.

[Emmaly]

Hi All,

I have to produce a spreadsheet that is "readable" for our new banking system.

They have asked us to make a concatenated cell with a specified string of data inside. But they have also been very specific on how many spaces need to be in each field.

So i might have to have a 0 in one cell but it will need 4 blanks after it. The next cell might need the customer ID which in our case has 6 characters but the whole field length needs to be 10 long, so 4 blanks.

How would i do this? I don't want to have blank columns with spaces in and do (A1 & A2) to add the spaces on the end, i want it more clever than that so it looks at C2 (in my example) and knows that the field needs to be 4 long. One of character and 3 of blank.

I have attached a little example of what they want and what i have got and i have put comments on the cells that i need to change.

I want to write VBA for this eventually as we will be pulling our data out of our accounts packagke by OLE then run the macro and it will sort the exported data into a format that the bank can read. But i need to know how to add the blank spaces in first.

Thank you!!

Emmaly

[Domski]

You might want to have a look at saving the data in a PRN format which is useful for creating fixed width text files. You just set the column width to the number of characters is field needs to be and it does the rest.

Dom

[Emmaly]

Hi Dom,

I can set the column width in a PRN but it does not seem to affect the concatenation. It does not insert any blank spaces into it.
I might be completely misunderstanding you though haha

Sorry i am not the brightest spark!

[Domski]

Forget the concatenation, you don't need to do that. Just set up your columns as the required fixed width, remove the headings if you don't want them and save the file as a prn file. You'll see that the file when you open it has the values with the required number of spaces after it.

Dom

[Emmaly]

Ok thanks, i will give it a go.

The only thing though is how wide is a character?

So the bank has specifed that the length of one of the fields needs to be 40. But 40 what? It is 40 characters but i have no idea how wide that is? Is it trial and error?

[Domski]

Just set the columns width for that field to 40. It can look a bit weird before you save it but it works.

Dom

[Emmaly]

OK thanks Dom,

I am not sure if this is compatible with the bank, but we can but try! I will send them up a sample and see what they say.

Thank you