Solved

Modify the spacing within cells in Excel

Posted on 2011-09-27
4
779 Views
Last Modified: 2012-05-12
Hello,

Is there any way to modify the spacing within cells in Excel (2010)?  I have never found any settings to affect this within the normal Excel controls.  However, I am wondering if it can be done through some kind of programming such as VBA.

Specifically, I am referring to the spacing settings which are managed by the Paragraph box in MS Word.  
 aThe settings there are grouped into three sections:

• General alignment -- this section is a non-issue because Excel's alignment settings include all the options found in Word and even more

•  Indentation -- although Excel does have indentation settings which affect the left margin of text, I have never found anything to alter the right margin or create first-line indentation or hanging.

• Spacing -- as far as I know, there are no options to change the inter-line settings in Excel.  Changing the font type and/or size affects the inter-line spacing slightly but customizing the spacing is not possible.

I have very little understanding of VBA but does it have the capability to affect the formatting of text within cells in the ways mentioned above?  As stated initially, I am mostly interested in being able to make changes in the third category:  inter-line spacing.

Thanks
0
Comment
Question by:Steve_Brady
4 Comments
 
LVL 33

Accepted Solution

by:
Rob Henson earned 300 total points
ID: 36710922
Steve,

I have found that Excel is not really designed particularly well to handle text publishing as well as MS Word would.

I have had numerous occasions where a report combines numerical data with text but need it formatted better than available in Excel - bullets, indents etc.

I have got round it in the past by embedding a Word document within an Excel sheet or embedding an Excel document within a Word document; depending on which there is the most of, words or numbers.

Alternatively, you can use a table within Word to get column and row alignment of numbers but there isn't the mathematical functions like there is in Excel. You can create the table of data in Excel and then copy and paste as a table, use Paste as RTF if you want the formatting.

Thanks
Rob H
0
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 100 total points
ID: 36711532
Steve_Brady,
I believe the best way to change the inter-line spacing is to add text to different rows and then adjust the row height.  Sorry, I know that's not the answer your were looking for.

http://office.microsoft.com/en-us/excel-help/change-line-spacing-in-excel-HA001034624.aspx

Kyle
0
 
LVL 4

Assisted Solution

by:pgorod
pgorod earned 100 total points
ID: 36923579
As far as the VBA you asked for is concerned, you can handle that sort of thing through Range's Interior object, like this:

Worksheets("Sheet1").Range("A1").Interior.ColorIndex = 3

Full options here: http://msdn.microsoft.com/en-us/library/ff839659.aspx

To  format only some characters within a cell, use the Characters() collection. See an example here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=743

However, you won't be able to do much regarding paragraph formatting. Only Font formatting options are available...
0
 

Author Closing Comment

by:Steve_Brady
ID: 36951990
Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question