blocks|key|1200619|text|您会得到#VALUE错误，因为格式字符串中包含双引号。如果您使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1200620|=TEXT(B2,"_($*+#,##0.00_);_($*+(#,##0.00);_($*+""-""??_);_(@_)")|code-block|syntax|javascript|1200621|使用双引号转义(通过加倍)，您将不会得到错误。但你不会得到想要的结果。在数字格式和文本函数将产生什么之间有一些细微的差别。我不知道他们都是什么，但这是一个。|1200622|您可以选择编写自己的函数。此函数将将会计格式的数字转换为字符串，并保留货币符号和第一个数字之间的空白。|1200623|Public+Function+GetRealText(Cell+As+Range)+As+String

++++Application.Volatile
++++GetRealText+=+Cell.Cells(1).Text

End+Function|1200624|不过，还是有个陷阱。如果调整列的大小，则Range.Text属性将更改，但此函数不会自动更新。即使是不稳定的，列宽度的变化也不会触发重新计算。您必须手动重新计算，否则就必须触发它。|1200625|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

You get the #VALUE error because the format string has double quotes in it. If you use

<pre><code>=TEXT(B2,"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)")
</code></pre>

with the double quotes escaped (by doubling them) you won't get the error. But you won't get the desired result. There are some subtle differences between number formats and what the TEXT function will produce. I don't know what they all are, but this is one.

One option you have is to write your own function. This function will convert the accounting formatted number into a string and preserve the white space between the currency symbol and the first number.

<pre><code>Public Function GetRealText(Cell As Range) As String

 Application.Volatile
 GetRealText = Cell.Cells(1).Text

End Function
</code></pre>

There is a catch, though. If you resize a column, the Range.Text property will change, but this function will not update automatically. Even though it's Volatile, a column width change won't trigger a recalc. You'd have to recalc manually or something else would have to trigger it.

blocks|key|3666408|text|您可以使用'前缀将数字、日期或任何值作为文本：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3666409|Cells(1,+1)+=+"'"+&+MyNumber|code-block|syntax|javascript|3666410|entityMap^0|5|1|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You can put a number, date or any value as text by using the <code>'</code> prefix:

<pre><code>Cells(1, 1) = "'" &amp; MyNumber
</code></pre>

blocks|key|1200707|text|将公式乘以1，然后包含公式的格式单元格作为会计|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1200708|Cell+A1+=5000
=TEXT(A1,"$#,##0.00")+gives+you+currency.
=TEXT(A1,"$#,##0.00")*1+now+it's+back+to+a+number+and+displays+as+your+cell+format.+Format+as+Accounting|code-block|syntax|javascript|1200709|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Multiply the formula times 1 and then the format cell containing the formula as Accounting
<pre><code>Cell A1 =5000
=TEXT(A1,&quot;$#,##0.00&quot;) gives you currency.
=TEXT(A1,&quot;$#,##0.00&quot;)*1 now it's back to a number and displays as your cell format. Format as Accounting
</code></pre>

Here's the deal. I'm using a code-based advanced filter to do a database search. The criteria (as generated by the code) treats all data as text and appends a * to the front and back end of anything entered to facilitate partial lookups.

One of my fields (just one, in the middle of the data, and indistinguishable otherwise) is formatted as "Accounting". I want to format it as text so that it is query-able. But when I use the TEXT() function to try and format it (as I do in any other case where I need numbers stored as text), it won't accept "Accounting" as one of the presets. And furthermore, if I attempt to copy-paste the format from Excel's own custom formats form like so: _(* #,##0.00_);_(* (#,##0.00);_(* "-"??);(@_), then I get a #VALUE error in the cell.

By selectively trimming that format string I've gotten to where it no longer presents an error, but cannot get the numbers in the column to align on the decimal (which is the point of Accounting format).

Please help.

Cast "Accounting" formatted numbers as text in Excel

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

这是交易。我正在使用一个基于代码的高级过滤器来进行数据库搜索。标准(由代码生成)将所有数据作为文本处理，并将*附加到输入的任何内容的前端和后端，以便于部分查找。我的一个字段(只有一个字段位于数据中间，否则无法区分)被格式化为“会计”。我想把它格式化为文本，这样它就可以查询.但是，当我使用TEXT()函数尝试并格式化它时...

问在Excel中将“会计”格式的数字转换为文本
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Excel中将“会计”格式的数字转换为文本EN