As a loyal reader mentions in the comments to my article about Formatting Worksheets, he wanted to apply different formatting to separate words in a single cell. Specifically, he had cells containing a surname, a comma, and then first name, such as:
His goal was to format the last name in bold, while leaving the first name in normal weight font:
(BTW, the sample names are mine. How many days until Opening Day? Or at least until pitchers and catchers report? But, alas, I digress...)
My suggested solution was to use the Characters object to get a reference to a specific subset of characters within the cell, rather than to all of the cell's text.
Call the Characters(start, length) method on a Range object (usually a cell) to return a reference to the specific characters. Note that the Characters object's index is 1-based.
Once you have your subset of characters you can apply Font formatting much like you would with a Cell object, setting the Font object's Name, Size, Bold, ColorIndex, and other properties.
So, assuming you have a worksheet object, ws, and you want to format the values in cells A1 through A3 as described above, try the following code:
ws.Range('A1:A3').Cells.each do |cell|
# find the index of the first comma:
comma = cell.Value.to_s.index(',')
# get the range of characters before the comma
lastname = cell.Characters(1, comma)
# get the range of characters after the comma
firstname = cell.Characters(comma + 2, cell.Value.to_s.size)
# apply formatting:
lastname.Font.Bold = true
firstname.Font.Bold = false
There you have it. Let me know if you have questions or comments.
Thanks for stopping by!