Lining up data from concatenation

  • Thread starter Thread starter Some Dude
  • Start date Start date
S

Some Dude

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

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

You will need to use a fixed width font like courier new to get th
effect to work properly
 
If your "project #" number never exceeds 9, how about just right
aligning Column C?

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

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
 
Hi Dude,

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

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

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