Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Lining up data from concatenation
Nope, Bad thought! It seems concatenate ignores the leading zeros.
|
#9
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Lining up data from concatenation
Not a bad thought.
Just do the formatting inside the formula. =CONCATENATE(TEXT(A1,"0000")," ",B1) |
#10
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
Importing Data | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |