excel 将不同单元格中的小写金额转换成大写金额

首先，我觉得你的思路要改一下，像你那样，即不方便输入，也不方便计算，修改为下图样式：

1、将M2：M3作为手工输入区域，在M4输入

=M2+M3

2、在B2输入

=LEFT(RIGHT(TEXT($M2/1%," ￥0; -￥0;"),11-COLUMN(A1)))

右拉填充公式到K2再下拉填充到K4，自动完成数据分割并填列到相应栏。

3、B5输入

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(M4),"[dbnum2]")&TEXT(RIGHT(M4/1%,2),"[dbnum2]元0角0分"),"零角","零"),"零分","整"),"零整","整")

将M4合计转换为大写。

选中A1：K5，设置页面打印区域（这样，打印时，就不可能打印出录入区的了）

具体见附件。

如果非要在你现有格式下得出结果，直接在你B5输入：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B4&C4&D4&E4&F4&G4&H4&I4,"[dbnum2]")&TEXT(J4&K4,"[dbnum2]元0角0分"),"零角","零"),"零分","整"),"零整","整")

如下D5中输入如下公式

=TEXT(TRUNC(VALUE(B4&C4&D4&E4&F4&G4&H4&I4&"."&J4&K4)),"[dbnum2]")&"元"&TEXT(MOD(VALUE(B4&C4&D4&E4&F4&G4&H4&I4&"."&J4&K4),1)*100,"00[dbnum2]")&"分"

=IF(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0)=B3&C3&D3&E3&F3&G3&H3&K3*1,TEXT(B3&C3&D3&E3&F3&G3&H3&K3*1,"[DBNum2]G/通用格式")&"元整",IF(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,1)=L4,TEXT(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0),"[DBNum2]G/通用格式")&"元"&TEXT((B3&C3&D3&E3&F3&G3&H3&K3*1-TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0))*10,"[DBNum2]0")&"角整",IF(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,1)=TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0),TEXT(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0),"[DBNum2]G/通用格式")&"元"&"零"&TEXT((B3&C3&D3&E3&F3&G3&H3&K3*1-TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,1))*100,"[DBNum2]0")&"分",TEXT(TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0),"[DBNum2]G/通用格式")&"元"&TEXT((B3&C3&D3&E3&F3&G3&H3&K3*1-TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,0))*10,"[DBNum2]0")&"角"&TEXT((B3&C3&D3&E3&F3&G3&H3&K3*1-TRUNC(B3&C3&D3&E3&F3&G3&H3&K3*1,1))*100,"[DBNum2]0")&"分")))&"整"

求和再用text函数