Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Modify the spacing within cells in Excel

Posted on 2011-09-27
4
Medium Priority
?
800 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 33

Accepted Solution

by:
Rob Henson earned 1200 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 400 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 400 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

610 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