Aligning Text 3

[NomLaser]

I'm using Excel 2007 to generate my quotes, and I need to concatenate a customers name and date but have them aligned left and right respectively in the same cell. Right now we just use spaces and it's a PITA to be honest. If only excel had the ability to split individual cells like you can in word's tables then it wouldn't be an issue. I know that I can add a new column but it screws up the rest of the document. Any ideas?

 

[zdas04]

Why don't you use two text boxes? They won't be referencable (is that a word?), but names are rarely used in calculations and if you are using one of the date functions (like =Now) then it is available as many times as you need it.

David

 

[zelgar]

I know this sounds like a silly work-around, but could you simply copy the table into Word, then split the cell and then copy it back into Excel?

 

[SteveGregory]

1. Insert a column next to the cells that you want to "split".
2. Merge all of the cells above and below the original cells that you wanted to "split".
3. Adjust the width of these columns.
4. Format the "split" cells for text alignment - voila!

 

[NomLaser]

I guess a textbox would be easiest. The problem is that I have a co-worker in his late 40's that's not the most computer literate person that I have met. (I had to teach him how to copy and paste text if that gives you an idea)

Steve, your suggestion is what I tried before and gets messy with the rest of the document. I was hoping that Excel had some type of column that you could format to and have each side aligned differently or perform a soft split of the cells. I'm starting to think that I should just use Excel for what it was designed for...lol

 

[IRstuff]

If you want to get somewhat fancy, you could determine the number of characters in the name, and the quote, and automatically generate the required spaces. For example, assuming Courier, otherwise they don't line up correctly:

CONCATENATE(D1,REPT(" ",40-LEN(D1)-LEN(TEXT(E1,"$ #####.00"))),TEXT(E1,"$ #####.00"))

Where D contains the text of the name, and E contains the dollar amount. This should result in a text column formatted for 40 characters wide.

http://files.engineering.com/downlo...48da-a9e1-a1f359e1a567&file=column_format.xls

TTFN

FAQ731-376

 

[NomLaser]

IRstuff

Problem is that we have the customer name and date in the same cell/row so that would work until the customer name changed. Good idea and we more along the line of what I was thinking with some formulas.

Along those lines if I knew how many characters it took to fill the line on the page (before it wrapped to another row), could you count the spaces in the name add it with the date and subtract that from the total?

 

[IRstuff]

That's what the formula does.

TTFN

FAQ731-376

 

[NomLaser]

ahhh i see....sorry i am at home and just have open office so I didn't bother trying it yet. thanks

 

[NomLaser]

IR...thanks for that code. It works pretty good for most combinations and is easy enough for someone else to figure out.

 

[IRstuff]

Thanks, glad to help.

TTFN

FAQ731-376