跳到主要内容

如何串联单元格忽略或跳过Excel中的空格?

Excel的 CONCATENATE 函数可以帮助您快速将多个单元格值合并到一个单元格中,如果所选单元格中有一些空白单元格,则此功能也将合并空白。 但是,有时候,您只想将单元格与数据连接起来并跳过空单元格,如何在Excel中完成呢?

串联单元格忽略或跳过带公式的空白

串联的单元格使用用户定义的函数忽略或跳过空格

使用 Kutools for Excel 连接单元格忽略或跳过空白


假设,我有以下单元格数据,其中填充了一些空白单元格,以将行合并为一个单元格,您可以应用公式来求解它。

doc合并跳过空格1

请将此公式输入到要放置合并结果的空白单元格中, =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,""),然后将填充手柄向右拖动到您要应用此公式的单元格上,并且行的值已合并而没有空白单元格,如以下屏幕截图所示:

doc合并跳过空格2

Tips:如果需要连接更多行,则应使用IF函数来连接单元格,例如 =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,"")&IF(A6<>"","-"&A6,"")。 这个“" 公式中的字符可以替换为所需的任何其他定界符。


连接多个单元格会忽略或跳过空格:

Kutools for Excel's 结合 行,列或单元格不丢失数据 功能可以帮助您合并或连接多个行,列或单元格,而不会丢失数据,但是可以跳过或忽略空白单元格。 点击下载Kutools for Excel!

doc合并跳过空格7

Kutools for Excel:具有300多个方便的Excel加载项,可以在30天内免费试用,没有任何限制。 立即下载并免费试用!


如果需要组合多个单元,则上面的公式将太复杂而无法执行,因此,您可以使用以下用户定义函数来解决它。

1。 按住 Alt + F11键 键,然后打开 Microsoft Visual Basic应用程序 窗口。

2。 点击 插页 > 模块,然后将以下宏粘贴到 模块 窗口。

VBA代码:串联单元格忽略空格:

Function Concatenatecells(ConcatArea As Range) As String
'updateby Extendoffice
  For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & "/"): Next
  Concatenatecells = Left(nn, Len(nn) - 1)
End Function

3。 保存并关闭此代码窗口,返回工作表,然后输入以下公式: = concatenatecells(A1:A5) 到一个空白单元格中,然后将填充手柄向右拖动到这些单元格上以应用此公式,您将得到以下结果:

doc合并跳过空格3

Tips:在上述VBA代码中,您可以更改“/”表示您需要的任何其他定界符。


除了无聊的公式和代码,在这里,我可以推荐一种方便的工具-Kutools for Excel,其功能强大 结合 实用程序,您可以毫不费力地将多个单元格连接成一个单元格。

Kutools for Excel : 带有300多个便捷的Excel加载项,可以在30天内免费试用

安装后 Kutools for Excel,请执行以下操作:

1。 选择要合并的单元格值。

2。 点击 库工具 > 合并与拆分合并行,列或单元格而不会丢失数据,请参见屏幕截图:

3。 在弹出的对话框中:

  • (1.)选择要合并的单元格类型,可以合并列,合并行并将所有单元格合并为单一单元格;
  • (2.)为连接的内容指定一个分隔符;
  • (3.)选择放置结果的位置,可以将结果放置在顶部单元格或底部单元格中;
  • (4.)指定如何处理合并的单元格,可以保留或删除那些合并的单元格中的内容,还可以合并这些合并的单元格。

doc合并跳过空格5

4。 完成设置后,单击 Ok 按钮,并且所选行已分别合并到一个单元格中,并且它自动跳过了空白,请参见屏幕截图:

doc合并跳过空格6

立即下载和免费试用Excel的Kutools!


Kutools for Excel:具有300多个方便的Excel加载项,可以在30天内免费试用,没有任何限制。 立即下载并免费试用!

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How would you change the "/" sign for CHAR(10) or line break?
This comment was minimized by the moderator on the site
Hello, Alan,To combine the cells with the line break, you can apply the below simple formula:=TEXTJOIN(CHAR(10),TRUE,A1:A5)
After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
Please try, thank you!
This comment was minimized by the moderator on the site
super, thank Q
This comment was minimized by the moderator on the site
I had issues with the formula provided causing leading delimiters, so I did this instead and it works well. This is concatenating cells horizontally while ignoring blank cells and results in no extra commas.

=IF(A2="", "", A2)&IF(A2="", "", ", ")&IF(B2="", "", B2)&IF(B2="", "", ", ")&IF(C2="", "", C2)
This comment was minimized by the moderator on the site
This worked a treat, thanks so much
This comment was minimized by the moderator on the site
Hi, used the macro and changed the "/" to a comma "," but got a lot of commas and it appeared to add all the blank cells.
I am doing a nested if statement to determine the appropriate sorting in the database. Is this enough to make the blank cell 'active' so that the macro sees this and adds it to the text string? How to work around that?
thanks much
This comment was minimized by the moderator on the site
Hello, Melinda,
the above vba code works well in my worksheet, you just need to change the separator / to comma as below:

Function Concatenatecells(ConcatArea As Range) As String
'updateby Extendoffice 20151103
For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & ","): Next
Concatenatecells = Left(nn, Len(nn) - 1)
End Function

and then apply this formula:=concatenatecells(A1:A5)
This comment was minimized by the moderator on the site
thanks!
I found my problem was in the logic statement that I used to select data for these cells that I was trying to text string. I used a " " instead of "" for the false statement. That was picked up by the macro and used as a space bar and came out , , , , , text, , , ,
So I went back and took out the space and just have the "" and then the macro worked great.
Of course I am learning macros so that's another adventure.
thanks much!!
This comment was minimized by the moderator on the site
Thank you, it was very helpful!
This comment was minimized by the moderator on the site
Please help, i dont always have a value in my first column, that couses that I end up with a seperator infort of the final result. Is there a way around this?
This comment was minimized by the moderator on the site
ever get tis figured out? same boat.
This comment was minimized by the moderator on the site
I don't know how to do it all in one cell, but I added a row with this formula: =IF(LEFT(U20,1)=",",RIGHT(U20,LEN(U20)-2),U20).
My separator was a comma and a space ", " so I used -2 for LEN. U20 is the cell with the concatenated &if formula. The logic of this formula is that if the first character from the left equals a comma, then delete the first two characters; otherwise leave it alone.

Hope this helps.
This comment was minimized by the moderator on the site
Thank you! This saved hours of frustration on my part! Works as a charm!
This comment was minimized by the moderator on the site
The VBA script is wrong, because the output of the formula puts a huge space between the delimiters.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations