如何在Excel 2016或LibreOffice中将单元格文本转为带分隔符的ASCII序列？

需求要点 #

• 输入字符ASCII值范围0-255
• 输入字符串长度通常≤4，最多10个字符
• 输出以空格分隔的ASCII码序列，末尾无多余分隔符
• 公式兼容Excel 2016及LibreOffice

解决方案 #

方案1：手动拼接式公式（推荐，无数组输入要求） #

=IF(LEN(A1)>=1,CODE(MID(A1,1,1)),"")&IF(LEN(A1)>=2," "&CODE(MID(A1,2,1)),"")&IF(LEN(A1)>=3," "&CODE(MID(A1,3,1)),"")&IF(LEN(A1)>=4," "&CODE(MID(A1,4,1)),"")&IF(LEN(A1)>=5," "&CODE(MID(A1,5,1)),"")&IF(LEN(A1)>=6," "&CODE(MID(A1,6,1)),"")&IF(LEN(A1)>=7," "&CODE(MID(A1,7,1)),"")&IF(LEN(A1)>=8," "&CODE(MID(A1,8,1)),"")&IF(LEN(A1)>=9," "&CODE(MID(A1,9,1)),"")&IF(LEN(A1)>=10," "&CODE(MID(A1,10,1)),"")

• 直接输入即可生效，无需按数组快捷键
• 严格适配最多10个字符的限制，自动避免末尾出现多余分隔符
• 全版本兼容Excel 2016和LibreOffice

方案2：数组公式（自动适配长度） #

=LEFT(CONCAT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))&" "),LEN(CONCAT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))&" "))-1)

• Excel 2016中需按Ctrl+Shift+Enter确认输入；LibreOffice可直接回车或按相同快捷键
• 自动适配任意长度（≤10）的输入字符串
• 若需处理空输入，可嵌套优化为：=IF(LEN(A1)=0,"",LEFT(CONCAT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))&" "),LEN(CONCAT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))&" "))-1))

示例验证 #

• 输入abc → 输出97 98 99
• 输入YZ[ → 输出89 90 91
• 输入T → 输出84

内容的提问来源于stack exchange，提问作者kaanchan