Lining up data from concatenation
 

A1 = 1234
A2 = 5

B1 = project 1
B2 = project 2

If I do this in C1
=concatentate(A1," ",B1)
I get
1234 project1
for C1. That's fine but doing the same thing for row 2 gets me
5 project 2

I need a way to do this (without a macro because I'm totally helpless when
it comes to macros) so that Column C lines up everything in Column A AND
everything in column B so the data is in 1 cell and the "p" in project lines
up vertically no matter how many characters are in the number in Column A.
The data HAS TO be in a single cell - no merging cells and changing border
colors to make it appear that way.
I think I need something in my concatenate that assigns a set number of
spaces to the data in Column A and concatenates Column B data starting in
the same position every time.
But how?

Lining up data from concatenation
 

Try

=A1 & LEFT(" ",4-LEN(A1)) & B1

You will need to use a fixed width font like courier new to get the
effect to work properly.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=547979

Lining up data from concatenation
 

If your "project #" number never exceeds 9, how about just right
aligning Column C?

Craig

Some Dude wrote:

A1 = 1234
A2 = 5

B1 = project 1
B2 = project 2

If I do this in C1
=concatentate(A1," ",B1)
I get
1234 project1
for C1. That's fine but doing the same thing for row 2 gets me
5 project 2

I need a way to do this (without a macro because I'm totally helpless when
it comes to macros) so that Column C lines up everything in Column A AND
everything in column B so the data is in 1 cell and the "p" in project lines
up vertically no matter how many characters are in the number in Column A.
The data HAS TO be in a single cell - no merging cells and changing border
colors to make it appear that way.
I think I need something in my concatenate that assigns a set number of
spaces to the data in Column A and concatenates Column B data starting in
the same position every time.
But how?

Lining up data from concatenation
 

=A1 & LEFT(" ",4-LEN(A1)) & B1

That returns #VALUE! if LEN(A1)4.

=A1&" "&B1&REPT(" ",3-LEN(SUBSTITUTE(B1,LEFT(B1,8),"")))

Replace 3 with the maximum number of digits that any project number will
contain.

Project 1 = 1 (in this case, you can just right align the column)
Project 10 = 2
Project 100 = 3

If need be, that value can be calculated but will make the formula longer
and more complicated.

Biff

"mrice" wrote in
message ...

Try

=A1 & LEFT(" ",4-LEN(A1)) & B1

You will need to use a fixed width font like courier new to get the
effect to work properly.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile:
http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=547979

Lining up data from concatenation
 

On Fri, 2 Jun 2006 15:24:50 -0500, "Some Dude" wrote:

A1 = 1234
A2 = 5

B1 = project 1
B2 = project 2

If I do this in C1
=concatentate(A1," ",B1)
I get
1234 project1
for C1. That's fine but doing the same thing for row 2 gets me
5 project 2

I need a way to do this (without a macro because I'm totally helpless when
it comes to macros) so that Column C lines up everything in Column A AND
everything in column B so the data is in 1 cell and the "p" in project lines
up vertically no matter how many characters are in the number in Column A.
The data HAS TO be in a single cell - no merging cells and changing border
colors to make it appear that way.
I think I need something in my concatenate that assigns a set number of
spaces to the data in Column A and concatenates Column B data starting in
the same position every time.
But how?


You could try something like:

=CONCATENATE(REPT(" ",6-LEN(A1)),A1," ",B1)

Replace 6 by the largest length of the numbers in column A.

You will need to use a fixed space font -- something like Courier New -- in
order to align this properly.


--ron

Lining up data from concatenation
 

After revisiting this and seeing the other replies:

Assuming that the values in column A are always numbers:

=REPT(" ",LEN(MAX(A$1:A$10))-LEN(A1))&A1&" "&B1

Use a fixed width font.

Biff

"Some Dude" wrote in message
...
A1 = 1234
A2 = 5

B1 = project 1
B2 = project 2

If I do this in C1
=concatentate(A1," ",B1)
I get
1234 project1
for C1. That's fine but doing the same thing for row 2 gets me
5 project 2

I need a way to do this (without a macro because I'm totally helpless when
it comes to macros) so that Column C lines up everything in Column A AND
everything in column B so the data is in 1 cell and the "p" in project
lines up vertically no matter how many characters are in the number in
Column A.
The data HAS TO be in a single cell - no merging cells and changing border
colors to make it appear that way.
I think I need something in my concatenate that assigns a set number of
spaces to the data in Column A and concatenates Column B data starting in
the same position every time.
But how?

Lining up data from concatenation
 

Hi Dude,

Another possibility is to format your initial data as custom '0000'.
Which means 5 will become 0005.

Just a thought
Martin

Lining up data from concatenation
 

Nope, Bad thought! It seems concatenate ignores the leading zeros.

Lining up data from concatenation
 

Not a bad thought.
Just do the formatting inside the formula.
=CONCATENATE(TEXT(A1,"0000")," ",B1)

Lining up data from concatenation
 

Brilliant! Thank you

"Ron Rosenfeld" wrote in message
...
On Fri, 2 Jun 2006 15:24:50 -0500, "Some Dude" wrote:

A1 = 1234
A2 = 5

B1 = project 1
B2 = project 2

If I do this in C1
=concatentate(A1," ",B1)
I get
1234 project1
for C1. That's fine but doing the same thing for row 2 gets me
5 project 2

I need a way to do this (without a macro because I'm totally helpless when
it comes to macros) so that Column C lines up everything in Column A AND
everything in column B so the data is in 1 cell and the "p" in project
lines
up vertically no matter how many characters are in the number in Column A.
The data HAS TO be in a single cell - no merging cells and changing border
colors to make it appear that way.
I think I need something in my concatenate that assigns a set number of
spaces to the data in Column A and concatenates Column B data starting in
the same position every time.
But how?


You could try something like:

=CONCATENATE(REPT(" ",6-LEN(A1)),A1," ",B1)

Replace 6 by the largest length of the numbers in column A.

You will need to use a fixed space font -- something like Courier New --
in
order to align this properly.


--ron

Lining up data from concatenation
 

On Mon, 5 Jun 2006 12:11:59 -0500, "Some Dude" wrote:

Brilliant! Thank you


Glad it worked for you. Thanks for the feedback.
--ron