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?

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?
>
>

> =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" <mrice.28sl4m_1149280801.2855@excelforum-nospam.com> wrote in
message news:mrice.28sl4m_1149280801.2855@excelforum-nospam.com...
>
> 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
>

On Fri, 2 Jun 2006 15:24:50 -0500, "Some Dude" <sdatt@myplace.com> 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

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" <sdatt@myplace.com> wrote in message
news:OmXBeKohGHA.2340@TK2MSFTNGP03.phx.gbl...
> 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?
>
>

Hi Dude,

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

Just a thought
Martin

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

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

Brilliant! Thank you

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:v5k18293pgnvnmb9l965ln7b7cn7cfjr98@4ax.com...
> On Fri, 2 Jun 2006 15:24:50 -0500, "Some Dude" <sdatt@myplace.com> 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

On Mon, 5 Jun 2006 12:11:59 -0500, "Some Dude" <sdatt@myplace.com> wrote:

>Brilliant! Thank you
>

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