使用公式在Excel计算单元中格式化文本子集

I Have built a string using a formula in excel. as an example

我使用excel中的公式构建了一个字符串。作为一个例子

Cell C3 contains text "Languages"
Cell C4 = "English, Spanish,German, French"
My Forumla = C3 & ":" & CHAR(10) & C4

Cell C3包含文本"Languages" Cell C4 = "English, Spanish,German, French" My Forumla = C3 & ":" & CHAR(10) & C4

The Desired text would be:

所需的案文将是:

Languages:
English, Spanish, German, French

语言:英语、西班牙语、德语、法语

(where the bold text would actually be some color like red)

(粗体字实际上是一些颜色，比如红色)

Is there a way to do this in Excel (change partial text formatting) .

在Excel中是否有这样的方法(更改部分文本格式)。

I Have tried a formula... (Not working)

我试过一个公式……(不工作)

Function formatText(InText As Range)

'Set font color
  InText.Characters(1.5).Font.Color = Red
   'InText.Characters((InStr(1, ":", InText) + 1), (Len(InText) - InStr(1, ":", InText))).Font.ColorIndex = 3
End Function

4 个解决方案

#1

Your posted function with work if and only if

如果且仅当

It is called from a Sub (ie, as other have mentioned, not as a UDF)
它是从一个Sub(例如，正如其他人提到的，不是UDF)调用的

And

和

the value(s) contained in range InText are string constants. (This is the main point of my answer)
范围InText中包含的值是字符串常量。(这是我回答的要点)

It will not work for any cells in range InText containing a formula. AFAIK you cannot format part of a string returned by a formula.

它不能用于包含公式的范围内的任何单元格。如果不能格式化由公式返回的字符串的一部分。

BTW I would love to be proved wrong on this!

顺便说一句，我希望在这件事上被证明是错误的!

#2

Regarding Hightower's question, "how would you cast a formula output to a string so that you can apply the text formatting?"

关于Hightower的问题，“如何将公式输出转换为字符串，以便应用文本格式?”

To "cast" the output of a formula so that you can apply text formatting, you must write the value returned by the formula into the spreadsheet, then apply the formatting to the value you wrote. You could either write the value into the cell containing the formula (which will erase the formula), or you could write the value into a different place in the spreadsheet (which will preserve the formula but then you'll be seeing double).

要“强制转换”公式的输出，以便应用文本格式，您必须将公式返回的值写入电子表格，然后将格式应用到所编写的值。您可以将值写入包含公式的单元格中(它将擦除公式)，也可以将值写入电子表格中的不同位置(它将保留公式，但您将看到double)。

Sub Cell_Format(InText as Range)
  InText.formula = cstr(InText.value)  ' converts result of formula into a string literal   
    'or: InText.offset(0,1).formula = cstr(InText.value) -- writes the value in the cell next to InText
  InText.characters(1, 5).font.color = vbRed
End Sub

Then Cell_Format range("$A$1") will replace the formula in cell $A$1 with a string constant and change the color of the first five characters to red.

然后Cell_Format range(“$A$1”)将用一个字符串常量替换单元格$A$1中的公式，并将前5个字符的颜色改为红色。

If you want to do this for a range larger than one cell, add this code to the above:

如果您想对大于一个单元格的范围进行此操作，请将此代码添加到上面:

Sub Range_Format(InText as Range)
  For each c in InText
    Cell_Format(c)
  Next
End Sub

#3

You need to use this code:

您需要使用以下代码:

InText.Characters(1,5).Font.Color = RGB(255, 0, 0)

If you want to make it flexible, so that only the (fully) second line is red, use this code:

如果你想使它灵活，只有(完全)第二行是红色，请使用以下代码:

InText.Characters(Instr(InText, vbCr)+1, Len(InText)-Instr(InText, vbCr)).Font.Color = RGB(255, 0, 0)

Note that your function needs to be called from VBA, i.e. you cannot use it as a User-Defined-Function! UDFs can only return a result but never modify a cell!

请注意，您的函数需要从VBA调用，即不能将其用作用户定义的函数!udf只能返回一个结果，但不能修改单元格!

#4

You cannot directly call the below UDF in Excel interface. For that you will have use an event as UDF cannot change the physical characteristic of a cell. For more details you may read this link. http://support.microsoft.com/kb/170787

无法在Excel界面中直接调用下面的UDF。为此，您将使用一个事件，因为UDF不能更改单元格的物理特性。有关更多细节，请阅读此链接。http://support.microsoft.com/kb/170787

Function formatText(InText As Range)

'Set font color
  InText.Interior.Color = vbRed
   'InText.Characters((InStr(1, ":", InText) + 1), (Len(InText) - InStr(1, ":", InText))).Font.ColorIndex = 3
End Function

#1