How to bold certain text in a formulated cell?

February 1, 2017 at 18:16:09
Specs: Windows 7
I have to do up an automated letter using excel which uses a lot of referencing to other cells in the same or/and another worksheet. However, the automated letter may require some words in that cell to be bold/underline.

FORMULATED CELL:
="Please send us a crossed cheque for the total sum of $"&G19 &" within 30 days with effect from the date of this letter."

TEXT APPEARS AS:
Please send us a crossed cheque for the total sum of $2230 within 30 days with effect from the date of this letter.

And I would like the amount $2230 to be bold and underline, which is the cell refence from "G19". Please assist.

Thank you in advance!


See More: How to bold certain text in a formulated cell?

Report •

#1
February 1, 2017 at 18:54:46
It can not be done with a formula.
The only way I can think of is to split your sentence into three parts:

F19 ="Please send us a crossed cheque for the total sum of"
G19 $2230
H19 ="within 30 days with effect from the date of this letter."

Then all three together appear as one line.

Only other way would probably be with a Macro, but my skills at that
are just above nil.

MIKE

http://www.skeptic.com/


Report •

#2
February 1, 2017 at 19:11:56
You can not format only a portion of the result of a formula. When a cell contains a formula, the format must be applied to the entire cell.

There are 2 options that I can think of:

1 - Split the text into 3 cells and size them so that the end result looks like a sentence.
Format the middle cell as bold and underline and use that cell to store or reference your cheque amount.

2 - Use a Worksheet_Change macro to write the entire "sentence", including the cheque amount, as a text string and then have the macro bold/underline the cheque value.

The technique is explained in Response #4 in this thread:

https://www.mrexcel.com/forum/excel...

If you need help with that, let me know.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
February 1, 2017 at 19:13:12
It might not be best to split the sentence into 3 cells because then the automated letter would look weird and long horizontally. Besides, I have other paragraphs before and after that cell. If I were to fit the sentence in F19, the other paragraphs may not have the same cell width.

This is hard. But thanks @mmcconaghy for the suggestion. :)

message edited by dswryn


Report •

Related Solutions

#4
February 1, 2017 at 19:24:11
Should I mention that I merge and wrap text the cell? So the whole paragraph is actually from B21:H21. Will that make any difference to the coding?

Report •

#5
February 1, 2017 at 19:39:42
Here's the main problem:

You are trying to use a spreadsheet application as a word processor. While Excel does allow text in a formula and even offers some Text related functions, it's not a text based application.

Have you considered using Microsoft Word's Mail Merge functionality to create your letters? I believe that you could format the fields any way you want and then pull the required data in from Excel.

I'm not a Word expert by any means, but it seems more like the proper application for creating letters than Excel.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 1, 2017 at 19:47:45
I am more than glad to use Microsoft Word but my supervisor wants it all in Excel. Meaning that one worksheet for computation, data input and another worksheet for the automated letter. I've been at it for weeks and asking everyone I know. This is my last resort.

Thanks for your help anyways @DerbyDad03. I will try that 2nd technique you've suggested. :)


Report •

#7
February 2, 2017 at 05:51:28
You should be aware that if you go the macro path then everyone that uses the workbook will need to have macros enabled on their system. That is not something that can be done automatically. Each machine may need to be visited unless you have a robust enough IT department that can log into each machine and make those changes remotely.

There is a technique that can be employed to "force" users to enable macros by using a "Welcome Page" in the workbook. The Welcome Page will inform users that they will not be able to see or use the rest of the workbook until they enable macros on their system.

Various versions of this technique can be found on the web. DAGS force users to enable macros for links to the technique.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question