Bakerman

I should know this but ......

cell A1 is A
cell B1 is 4444

if I do ("0000000" + B1) in cell C1 then I have 0004444.

How do I get A0004444 ? Can't use concatenate as it uses the value held by Excel, not the format I have put it in.

Any ideas would be great, thanks

Rob B

If I'm reading your question correctly then you can put '000 in a cell somewhere away from the data in your columns. eg E1

Then (as long as each B value is 4 digits) enter as the formula CONCATENATE (A1&$E$1&B1). If you then want to copy this down the column for a number of rows you can do so. If some of the figures in column B are more than 4 digits, you'd need to specify another ref cell in the same way as E1, but with one less zero.

If this doesn't make sense, or isn't what you were after I apologise but its still a bit early!

Cheers,

Rob

dsmith

Excel String formulae again

Use +A1&C1

This will add them together as text strings so keeping any formatting you have imposed.

Bakerman

Thanks gents, I was trying to use the FORMAT command, if I use the TEXT command it works a treat (don't need to format the cell before using the TEXT cmd).

eg. =A1&text(B1,"0000000") incase anybody else is interested.