Excel help: Overwriting a cell containing a fixed number of characters, but...

[Dave]

...it's not that straightforward.

I need to set up a row of cells in Excel. Each cell must contain a fixed number of characters as a default.

The data points I'm adding to the cells (alphanumeric strings mostly), are mostly made up of fewer characters than the default.

What I want to do is overwrite the fixed characters with the new, but retain the default fixed length of the cell contents.

Does that make any sense? Probably not...

OK. An example. Cell A1 must contain 12 characters. I'm writing a 6 character string into A1. But A1 must still contain 12 characters after I've added the 6 character string.

How do I do that?

And please don't start your answer "By not using Excel..."  It's all I've got to work with.

(And damn you QCA and your chuffing irritating formatting requirements.)

[Séamas M.]

Quick and dirty way.

Set up a row somewhere below your data, say starting at A4 and put the following formula into the cells;

IF(LEN(A1)>=12,LEFT(A1,12),LEFT(CONCATENATE(A1,"123456789012"),12))

Replace the numbers within the quotes in the second part of the concatenate function with whatever filler characters you want.

Edit: Enter the formula into A4 as written above (after an "=") and then drag it across to the right to match the data entries in Row 1.  Excel should manage to sort out the cell addresses in the formula because it's written with relative addressing so the formula in D4 should read as follows;

=IF(LEN(D1)>=12,LEFT(D1,12),LEFT(CONCATENATE(D1,"123456789012"),12))

or if you want to use # as the filler edit the filler numbers so it looks like this;

=IF(LEN(D1)>=12,LEFT(D1,12),LEFT(CONCATENATE(D1,"############"),12))

For a string length of 10 it becomes;

=IF(LEN(D1)>=10,LEFT(D1,10),LEFT(CONCATENATE(D1,"##########"),10))

[Tim]

It really depends how you want the rest of the cell completed.

If you just wish to add trailing spaces I would suggest:

=a1 & rept(" ",max(12-len(a1),0))

[Dave]

If you just wish to add trailing spaces I would suggest:

See, now why couldn't I describe the problem that simply?  <sigh>

Thanks for the advice, it's much appreciated.

[Pingu]

Something like this will replace cell contents with 12 character long strings:



If the string is <12 characters it adds spaces to the beginning, if >12 it uses the first 12 characters.

[Dave]

Erk. I was hoping to keep well away from Visual Basic 

Thanks though, it looks like I may end up learning something useful from all this