Excel fixed fields length

[leighturner]

Take the following data as an example:

Field1 Field2 Field3
078 Rushden 1

I need to prefix field1 with a 01
Field2 needs to be a field length of 20 i.e. 20 spaces
Field3 needs to be prefixed with 10000 before the data starts.

End data to look like the following:

Field1 Field2 Field3
01078 Rushden************* 100001

(* = Spaces)

I will then be exporting the data to a .CSV file.

Regards,

Leigh

Leigh D Turner (BSc)

 

[xlbo]

Field 1:

="01" & A2

Field 2:
=B2 & Rept("0",20-Len(B2))

Field3:

="10000" & C2

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[leighturner]

That is not right!

Yes it works for the first columns but not the rest.

And I want spaced as apposed to zeros.

Leigh D Turner (BSc)

 

[arthurbr]

Hello Leigh,
just to remind you that Tek-Tips is not an Helpdesk, but just a bunch of guys (and girls)trying to help you out gracefully when all you tried has failed.
So if you "want" something, maybe you could take advice from people you will pay for rendered services.

regards

André

 

[PGoRule]

Use " " instead of "0".

Sharing the best from my side...

--Prashant--

 

[leighturner]

Many thanks all.

Leigh D Turner (BSc)