如何确保在列中格式化为文本的所有excel单元格实际上都是 [英] How to make sure all excel cells formatted as text in a column actually are

问题描述

我有一个由多个用户提交的内容，一般粘贴到一个工作表中，从多个来源。此列的数字应始终格式化为文本。
事实上，无论如何做，总是有一些项目，左角上没有指示器警告这些项目被格式化为文本（我们希望在所有情况下看到）

检查单个单元格，它显示为格式化的文本，但实际上导入到数据表中，如果指示符丢失，则将数据类型导入为数字。
点击数字后按Enter键将指示符更改为文本。

如何在VBA中执行此操作？我不想访问每个单元格，点击内容的结尾并点击enter.Cutting和粘贴特殊的组合可靠地修复这些。

excel看什么，这得到格式问题，这些文本格式警告指示符，但似乎没有正确的，当你看着单元格格式属性？

解决方案

对我来说有用的是检查错误指示器，它比单元格格式本身更可靠。这样会找到缺少指示符的东西，并强制它是文本。

除非有人进一步了解为什么不能做到这一点，否则可以解决我的问题。

  Sub check4textformat（）
对于范围内的每个单元格（E2：E15000）
如果单元格.Errors.Item（xlNumberAsText）.Value = False然后cell.Formula = cell.Text 
下一个
 End Sub 
  

I have a column of content submitted by multiple users, generally pasted into a sheet, from multiple sources. This column has numbers that should always be formatted as text. In fact, no matter how this is done, there are always a few items that never have the indicator in the left corner warning that these are formatted as text (which we want to see in all cases)

Checking the individual cell, it does show as formatted text, but in reality on an import into a datatable, if the indicator is missing, the datatype is imported as a number. Clicking after the number and hitting Enter will change the indicator to text.

How can I do that in VBA? I don't want to visit each cell, click on the end of the content and hit enter.Cutting and paste special in no combination reliably fixes these.

What does excel look at, which gets the format issue right with these text format warning indicators, and yet doesn't seem to get it right when you look at the cell format properties?

Excel 2003 but have had the same issue in later versions too.

解决方案

What worked for me was to check the error indicator, which seemed more reliable than the cell format itself. This looks for anything missing the indicator and forces it to be text.

Unless someone knows something further concerning why this should NOT be done, it solves my issue.

Sub check4textformat()
 For Each cell In Range("E2:E15000")
 If cell.Errors.Item(xlNumberAsText).Value = False Then cell.Formula = cell.Text
 Next
End Sub

这篇关于如何确保在列中格式化为文本的所有excel单元格实际上都是的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！