跳到主要內容

如何串聯單元格忽略或跳過Excel中的空格?

Excel的 CONCATENATE 函數可以幫助您快速將多個單元格值合併到一個單元格中,如果所選單元格中有一些空白單元格,則此功能也將合併空白。 但是,有時候,您只想將單元格與數據連接起來並跳過空單元格,如何在Excel中完成呢?

串聯單元格忽略或跳過帶公式的空白

串聯的單元格使用用戶定義的函數忽略或跳過空格

連接單元格使用Kutools for Excel忽略或跳過空白


假設,我有以下單元格數據,其中填充了一些空白單元格,以將行合併為一個單元格,您可以應用公式來求解它。

doc合併跳過空格1

請將此公式輸入到要放置合併結果的空白單元格中, =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,""),然後將填充手柄向右拖動到您要應用此公式的單元格上,並且行的值已合併而沒有空白單元格,如以下屏幕截圖所示:

doc合併跳過空格2

保養竅門:如果需要連接更多行,則應使用IF函數來連接單元格,例如 =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,"")&IF(A6<>"","-"&A6,"")。 這個“ - " 公式中的字符可以替換為所需的任何其他定界符。


連接多個單元格會忽略或跳過空格:

Excel的Kutools's 結合 行,列或單元格不丟失數據 功能可以幫助您合併或連接多個行,列或單元格,而不會丟失數據,但是可以跳過或忽略空白單元格。 點擊下載Kutools for Excel!

doc合併跳過空格7

Excel的Kutools:具有300多個方便的Excel加載項,可以在30天內免費試用,沒有任何限制。 立即下載並免費試用!


如果需要組合多個單元,則上面的公式將太複雜而無法執行,因此,您可以使用以下用戶定義函數來解決它。

1。 按住 Alt + F11鍵 鍵,然後打開 Microsoft Visual Basic for Applications 窗口。

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

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


除了無聊的公式和代碼,在這裡,我可以推荐一種方便的工具-Excel的Kutools,其功能強大 結合 實用程序,您可以輕鬆地將多個單元連接成一個單元。

Excel的Kutools : 帶有300多個便捷的Excel加載項,可以在30天內免費試用

安裝後 Excel的Kutools,請執行以下操作:

1。 選擇要合併的單元格值。

2。 點擊 庫工具 > 合併與拆分合併行,列或單元格而不會丟失數據,請參見屏幕截圖:

3。 在彈出的對話框中:

  • (1.)選擇要合併的單元格類型,可以合併列,合併行並將所有單元格合併為單一單元格;
  • (2.)為連接的內容指定一個分隔符;
  • (3.)選擇放置結果的位置,可以將結果放置在頂部單元格或底部單元格中;
  • (4.)指定如何處理合併的單元格,可以保留或刪除那些合併的單元格中的內容,還可以合併這些合併的單元格。

doc合併跳過空格5

4。 完成設置後,單擊 Ok 按鈕,所選的行已分別合併到一個單元格中,並且自動跳過了空白,請參見屏幕截圖:

doc合併跳過空格6

立即下載和免費試用Excel的Kutools!


Excel的Kutools:具有300多個方便的Excel加載項,可以在30天內免費試用,沒有任何限制。 立即下載並免費試用!

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為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