Concatenate Problem

J

John Calder

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?


Thanks

John
 
T

T. Valko

Can't you just remove all the extra spaces from the formula:

....CONCATENATE(I984&" [",B984&" DAYS ]"))

Or, am I missing something?
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
J

John Calder

Thanks for your prompt reply.

Removing the spaces does not make any differencce. I still would end up with
a staggerd text string. What I am aiming for is to have the source text
string aligned to the left and the conctenated text strig to the right

Hope this helps


John


T. Valko said:
Can't you just remove all the extra spaces from the formula:

....CONCATENATE(I984&" [",B984&" DAYS ]"))

Or, am I missing something?


--
Biff
Microsoft Excel MVP


John Calder said:
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?


Thanks

John
Click to expand...
Click to expand...
 
T

T. Valko

See Ron's reply. That should do what you want.

--
Biff
Microsoft Excel MVP


John Calder said:
Thanks for your prompt reply.

Removing the spaces does not make any differencce. I still would end up
with
a staggerd text string. What I am aiming for is to have the source text
string aligned to the left and the conctenated text strig to the right

Hope this helps


John


T. Valko said:
Can't you just remove all the extra spaces from the formula:

....CONCATENATE(I984&" [",B984&" DAYS ]"))

Or, am I missing something?


--
Biff
Microsoft Excel MVP


John Calder said:
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?


Thanks

John
Click to expand...
Click to expand...
Click to expand...
 
J

John Calder

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 said:
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?
Click to expand...

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
Click to expand...
 
J

John Calder

Ron

Just to let you know, I done as you suggested and the outcome was
outstanding.....many thanks and well done !



Ron Rosenfeld said:
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?
Click to expand...

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
Click to expand...
 
R

Ron Rosenfeld

Ron

Just to let you know, I done as you suggested and the outcome was
outstanding.....many thanks and well done !
Click to expand...

You're welcome. Glad to help. Thanks for the feedback.
--ron
 
Top