LinkBack URL
About LinkBacks
I'm putting a long line of text in the center header (left and right have something else) and it extends into the other header sections. Is there some way to wrap the text in the header so it doesn't over write the other sections?
I'm putting a long line of text in the center header (left and right have something else) and it extends into the other header sections. Is there some way to wrap the text in the header so it doesn't over write the other sections?
Select the cell with the long text. Click on the Home Tab, Wrap Text, then click on Format, AutoFit Row Height. You will likely have to manually adjust the width of the column to get a pleasing result.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I don't think you can. You'll need to enter a line break (ALT + Enter) to wrap the text.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Off topic: Oh, and from a regulated environment point of view, stay away from putting any sort of pertainent information into a header since there is no easy way to protect it from being changed. Even if the sheet and workbook are protected, the header can still be changed.
maybe enter a space in the next cell?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
No, it will just write on top of each other if the text is long enough. I did some research and Microsoft didn't think anyone would want that option. If the text is too long, add a manual line break. Sorry
maybe enter a space in the next cell?
Alt-Enter isn't needed to add a line break in the header. If you go into 'Page Layout' view (it's on the View tab) then you can just press Enter where you want the line break.
Regards,
Aardigspook
Confirmed that the order of entry doesn't matter. The left and right boxes should have short entries or short multi line entries.
ChemistB
You will get the over-writing if the entries in all three boxes exceed the allowable characters for a single line. You can have more than one line of text in each box.
Header two.JPG
Last edited by newdoverman; 07-10-2015 at 02:37 PM.
Sorry, mis-read the question - thought it was headings, not headers
Last edited by FDibbins; 07-10-2015 at 03:36 PM.
I tried the space and it worked for me no matter how long the text was.
Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.
<---Click * Add Reputation for all helpful comments. It's like giving a smile.
Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.
Sorry, misread the problem. In the header, if you hit enter before going outside the boundaries of the centre block of text for the header the text will stay inside that block. If you happen to go outside that block, the header appears to have only 1 section. This isn't true. The three sections are still there. Click ABOVE the header text and this is what you should see.
The Left and Right Boxes were filled in before the Centre box. Don't know if that makes a difference.
Header.JPG
I'm on Excel 2010 and I get the headers overwriting each other. See attached.
@Aardigspook Good point
The text is a combination of static text, file path, and filename. Some text is auto text from Excel, some is entered by the user in a specific cell which is then concatenated with the other text.
Enter or Alt-Enter isn't an option since the file path is usually where the break needs to occur.
I could insert a CR/LF every 50 characters or so but that makes the text hard to read.
Can you give examples of what you are dealing with....including some extremes in a workbook?
@Ford
I think we should go for a beer!!
no need for an reason (excuse?) for that, I will look you up next time we are in that area
@NewDover, yes, I get that there can be multiple lines in a box. I was just demonstrating Dr. Allen's point that it doesn't happen automatically, even when there's something in the side headers as nigel claimed.
@ChemistB
my initials are d.r. I only use Dr. in court or trying to impress the ladies.
Sorry I didn't realize it was it was the header either. I originally read it as if it were a problem with the column heading which would usually just be the first row.
@ChemistB
I understand. It would make a nice mod for the header and footer to have the margins for the 3 boxes variable with word wrap and other formatting incorporated instead of this manual thing that you have to "keep you wits about you" in order to get what you want.
One example is:
File Location: \MyLocation\MyDept Spreadsheets\client name\Test Method 9999\Sample identification data.xlsm
variable text would include:
MyDept
client name
Test Method number
Sample identification data
We can't use the autotext path because the official file is stored in a version controlled directory. When the file is edited, it's downloaded to a temp directory on the local hard drive and the initial file is locked. The path when it's loaded, printed, or saved is the local directory. The local file is deleted when it's moved back to the controlled directory. The filename will be the same so it's picked up as autotext. The static text at the beginning is the same so it's hard coded in. The rest is extracted from a worksheet and is unique for each client.
The left header section has the date and time printed. The right header section has page numbers. When the above text is inserted in the center section, it overwrites the other text.
The footer sections are populated as well so it can't be moved there plus some combinations are too long for a single line even if the other sections were blank. The sample identification data can be 35 to 40 characters
Last edited by dr01allen; 07-10-2015 at 04:47 PM.
This is the best that I can do by using a picture of what I wanted to put in the center section of the header.Test Header file.JPG
There may not be a solution that fits the URS. Which means we need to change the URS.
But I don't want to throw in the towel without at least getting it damp......
The only solution I can come up with is to move the multi-line section of the centre header down so it doesn't overwrite the left and right headers.
Here's a pic:
Longheadertext.png
As you can see from the pic, you'll need to make sure the worksheet margins are adjusted to give enough room for the header.
It's not an ideal solution, but might fit your needs until Microsoft give us the functionality to move the internal header margins.
Regards,
Aardigspook
You might be able to get the effect you want by placing what would be in the centre section within the first 4 or 5 rows of the worksheet merged with borders removed. To have this repeat on subsequent pages use the Print Titles dialogue and have those 4 or 5 rows repeated.
Result:
Header - ndm.JPG
I suggest you imbed your header into the first cells of the worksheet. Besides the benefit of text wrapping, it also can be protected while the headings can not be.
@ ChemistB
I used the first few rows of the worksheet as shown in msg#27. Is that pretty much what you had in mind?
Using Title Rows might be a solution. Our SOP says it must go in the header. Maybe I can expand the definition of header.
The only issue I've ever had with using embedded cells is the Page x of y ability. Can't do that within the spreadsheet without macros. So I usually keep that in the footer since it's not "critical or confidential information"
In one place I worked, I used imbedded Word Docs since their header formatting couldn't be reproduced by Excel. That's a pain though.
@newdover,
Yes, that would be it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks