Thanks Ron

This sounds like a bigger job than I thought. I am having to do all this
because all my colums of data are unique records and Excel 2K cannot handle
more than 8,000 of these so I am having to combine 2 columns of data to
lessen the load on the pivot table. I can probaly get by with what I have
(staggered text strings).

As this data is used for a pivot table that is viewed by a number of people
on server it would have been tidier to have the original text string aligned
to the left and the concatenated text string to the right.

I will give it a go and see how I go, thanks for your help

John





"Ron Rosenfeld" wrote:

On Tue, 1 Apr 2008 13:45:03 -0700, John Calder
wrote:

Hi

I use WinXP with Excel 2000

I use a "helper" column in a linked spread sheet with approx 1200 lines of
data that is then used to generate a pivot table.

The formula in the helper colum is as follows:

=IF(VALUE(F984)=0,"",CONCATENATE(I984&" [",B984&" DAYS ]"))

As you can see by the formula that I am adding a reference to another cell &
the word DAYS inside [] brackets from the text string in cell I984.

This works fine.

However as the original text string is of varying lengths the result is that
the data that I am concatenating is also staggered.

Example:

BLACK PB4W45855-PN84503-T [ 77 DAYS ]
BROWN PN3W45854-PN43009-T [ 36 DAYS ]
WHITE SPW0363X-PS13007-A [ 77 DAYS ]
E GREEN SPG0463X-PS53008-A [ 77 DAYS ]
RYSDALE BROWN-PN42789-D [ 103 DAYS ]


What I would like to be able to do is have the concatenated data to be
"right aligned" in the cell while the original data stays left aligned.

Example:

BLACK PB4W45855-PN84503-T [ 77 DAYS ]
BROWN PN3W45854-PN43009-T [ 36 DAYS ]
WHITE SPW0363X-PS13007-A [ 77 DAYS ]
E GREEN SPG0463X-PS53008-A [ 77 DAYS ]
RYSDALE BROWN-PN42789-D [ 103 DAYS ]

Is this possible?



If you can use a fixed-pitch font, then you can add in the appropriate number
of spaces using a formula. This won't work with a proportionally spaced font,
in which case you'll need to get into calls that compute the actual character
width. There's information about this on the 'net, but it's pretty complex.

You have to compute the proper number of spaces both before the "[" as well as
between the "[" and the number.

I used A19 and G19 for my sources, and Courier New for a font.

You may have to adjust the "30" and the "4" below depending on the range of
lengths of your data.

=IF(A19=0,"",CONCATENATE(A19,REPT(" ",30-LEN(A19)),"[",
REPT(" ",4-LEN(G19)),G19," DAYS]"))

--ron