×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Aligning Text
3

Aligning Text

Aligning Text

(OP)
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?

RE: Aligning Text

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

RE: Aligning Text

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?

RE: Aligning Text

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!

RE: Aligning Text

(OP)
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

RE: Aligning Text

3
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/download.aspx?folder=2b1f7311-da66-48da-a9e1-a1f359e1a567&file=column_format.xls

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: Aligning Text

(OP)
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?

RE: Aligning Text

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

RE: Aligning Text

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources