将格式化的符号添加到单元格中的文本，并保留现有文本的字符格式 [英] add formatted symbol to text in a cell, keeping character formats of pre-existing text

问题描述

这从我在

我不知道如何在单元格中插入字符并保留原有的字符格式(某些文本为红色，其他文本为粗体，其他文本可能带有下划线.)? /p>

我找到了一个宏，该宏替换了保留了现有格式，并且想知道是否可以将其更改为 add 单元格末尾的刻度和空格，并格式化它们?

我在更改时防止字符重新格式化的版本)单元格中的行:)

    STRINGTODELETE = InStr(1, cell.Value, " ") 'define + find string to delete, ANYWHERE in cell (TODO inputbox)
    STRINGTODELETE_LENGTH = 1  'define length of string to delete (TODO get length from input)

    Do Until STRINGTODELETE = 0 'loop within cell until no more occurences found
        cell.Characters(STRINGTODELETE, STRINGTODELETE_LENGTH).Delete 'remove found occurrence of SPECIFIED STRING
        cell.Characters(STRINGTODELETE, 0).Insert Chr(10) 'insert line break where SPECIFIED STRING used to be

解决方案

编辑:使用ActiveCell.Value或ActiveCell.Formula确实会更改您以前使用的任何特殊格式(我的初始文章忽略了此内容) .但是，随着研究的间接显示，您可以使用character.insert来更改文本，但是您需要具有插入位置的字符长度，以免更改自定义格式.

尝试使用此方法，而不要使用值或公式设置.

ActiveCell.Characters(ActiveCell.Characters.Count + 1, 1).Insert (" P ")

然后，您使用类似的方法继续更新倒数第二个字符(实际上是在最后一个字符之前编辑1个字符).

With ActiveCell.Characters(ActiveCell.Characters.Count -1, Length:=1).Font
        .Name = "Wingdings 2"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
End With

This continues on from my question at excel vba select second to last character in a cell

I am writing a macro to insert a red tick (or down arrow) after the existing text in a cell.

The code I found to insert it, removes the customised character formatting of the original cell contents (bold underlined red etc).

ActiveCell.FormulaR1C1 = ActiveCell & " P "

I can't work out how to insert the characters and retain the pre-existing character formats in the cell (some text red, other text bold, other text may be underlined.)?

I found a macro which replaces characters retaining the existing formats, and wondering if this might be changed to instead add the tick and spaces at the end of the cell, and format them?

I found another macro which copies the format of each character to a new cell after copying the value, at Merge contents of 2 Excel cells keeping character format intact (using VBA), if it could be tweaked to re-format the characters in the original cell... I am a bit stumped!

Or maybe there is an even simpler way?

Any assistance gratefully appreciated.

=============== (The replace macro (edited verson of Prevent reformatting of characters when changing line in cell:)

    STRINGTODELETE = InStr(1, cell.Value, " ") 'define + find string to delete, ANYWHERE in cell (TODO inputbox)
    STRINGTODELETE_LENGTH = 1  'define length of string to delete (TODO get length from input)

    Do Until STRINGTODELETE = 0 'loop within cell until no more occurences found
        cell.Characters(STRINGTODELETE, STRINGTODELETE_LENGTH).Delete 'remove found occurrence of SPECIFIED STRING
        cell.Characters(STRINGTODELETE, 0).Insert Chr(10) 'insert line break where SPECIFIED STRING used to be

解决方案

EDITED: Using ActiveCell.Value or ActiveCell.Formula will indeed change any SPECIAL formatting you previously had (my initial post overlooked that). However, as your research indirectly displayed, you can change the text by using character.insert, but you need to have the character length of where to insert to avoid changing your custom formatting.

Try this instead of value or formula setting.

ActiveCell.Characters(ActiveCell.Characters.Count + 1, 1).Insert (" P ")

Then you proceed to update the second to last character using a similar approach (this is essentially editing 1 character before the last character).

With ActiveCell.Characters(ActiveCell.Characters.Count -1, Length:=1).Font
        .Name = "Wingdings 2"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
End With

这篇关于将格式化的符号添加到单元格中的文本，并保留现有文本的字符格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！