改变细胞线时，防止重新格式化字符 [英] Prevent reformatting of characters when changing line in cell

问题描述

我有一个单元格，格式化的文本包含一个特定的子字符串，我想用换行符替换。子串是方括号的[Enterkey]。这个问题的一个变体是

我使用的代码是一个简单的替换，其中rng2是输入和输出单元格：

  rng2.Value = Replace（rng2.Value，[enterkey]，Chr（10））
  

我的问题

插入换行符时，如何防止该代码复制上述行的格式？我想要保存格式，只要插入一个换行符，如图所示。

---

更多信息

我实际上转换为HTML并返回。我不想添加不必要的细节，但如果人们真的想知道，那是因为我将值存储在Access数据库中，并且我想保留单元格格式。从Access中检索信息时，我想再次获得正确的格式。这个问题只涉及转换。有关详细信息，请参阅下图。

解决方案

经过一番研究，我得到了一些有用的东西。

此代码：

  Sub ReplaceEnterKey（）
 
 Dim rng2 As Range 
 Set rng2 = Range（O15）
 
 Dim iChr As Integer 
 iChr = InStr（1，rng2.Value，[e ）找到第一次发生的开始[enterkey]
 
直到iChr = 0循环，直到找不到更多的发现
 
 rng2.Characters（iChr，10）删除发现的[enterkey] -  10个字符
 rng2.Characters（iChr，0）.Insert Chr（10）'insert carriage return where[enterkey]曾经是
 
 iChr = InStr（1，rng2.Value，[e）'寻找下一次发生
 
循环
 
 End Sub 
  

创建了此结果。 N15 原始 O15 。

I have a cell with formatted text containing a certain substring that I want to replace with a line change. The substring is [enterkey] with the square brackets. It is a variant of this question Replace HTML tag(</br>) with Alt+Enter in Excel but I want to keep formatting after changing the line.

The code I am using is a simple replace where rng2 is the input and output cell:

rng2.Value = Replace(rng2.Value, "[enterkey]", Chr(10))

My Question

How can I prevent that code from copying the format of the line above when inserting a line feed? I want it to conserve the format AS IT IS and simply insert a line feed, like shown in the picture.

---

More info

I'm actually converting to HTML and back. I didn't want to add unnecessary details but if people really want to know, it's because I'm storing the values in an Access database and I want to keep cell formatting. When retrieving info from Access, I want to have the proper formatting again. This question only concerns the conversion back. See below picture for full details.

解决方案

After some research, I got something that works.

This code:

Sub ReplaceEnterKey()

Dim rng2 As Range
Set rng2 = Range("O15")

Dim iChr As Integer
iChr = InStr(1, rng2.Value, "[e") 'find beginning of first occurence of "[enterkey]"

Do Until iChr = 0 'loop until no more occurences found

    rng2.Characters(iChr, 10).Delete 'remove found occurrence of "[enterkey]" - 10 characters
    rng2.Characters(iChr, 0).Insert Chr(10) 'insert carriage return where "[enterkey]" used to be

    iChr = InStr(1, rng2.Value, "[e") 'look for next occurence

Loop

End Sub

Created this result. N15 was original O15.

这篇关于改变细胞线时，防止重新格式化字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！