Объединение столбцов с разделителем ', ' и не отображать разделитель, если не существует значения

Я объединяю данные в столбец A следующим образом:

=B2&", " &C2&", " &D2 

A        |  B | C  | D 
ES,,        ES      
DE,NL,IT    DE  NL  IT
CH,,        CH      
CH,,        CH      
DE,BE,AT    DE  BE  AT
FR,BE,      FR  BE  
CH,SG,      CH  SG  
AT,CH,ES    AT  CH  ES

Тем не менее, если в столбце нет значений, он добавляет, например, extra,, what I don't want. Так что результат должен быть как 

A        |  B | C  | D 
ES          ES      
DE,NL,IT    DE  NL  IT
CH          CH      
CH          CH      
DE,BE,AT    DE  BE  AT
FR,BE       FR  BE  
CH,SG       CH  SG  
AT,CH,ES    AT  CH  ES

Как это сделать?

3 3
excel excel-formula excel-2010

3 ответа:

Если вы используете Excel 2016, вы можете использовать функцию TEXTJOIN для объединения с разделителем, который также позволяет пропускать пустые ячейки.

Например: 

=TEXTJOIN(",",TRUE,B2:D2)

Вернет: 

A        |  B  |  C  |  D 
ES          ES      
DE,NL,IT    DE    NL    IT
4

Используйте эту формулу =B2&IF(C2<>"",","&C2,"")&IF(D2<>"",","&D2,"")...

Или

Объединять ячейки игнорировать или пропускать пробелы с помощью определяемой пользователем функции

Если требуется объединить несколько ячеек, приведенная выше формула будет слишком сложной для выполнения, поэтому для ее решения можно использовать следующую пользовательскую функцию.

  1. Удерживайте клавиши Alt + F11 в Excel, и откроется окно Microsoft Visual Basic для приложений.

  2. Щелкните вставить > модуль и вставьте следующий макрос в окне модуля.

Код VBA: объединение ячеек игнорирование пробелов: 

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
  1. сохраните и закройте это окно кода, вернитесь на рабочий лист, затем введите следующую формулу: just ex : =concatenatecells (A1:A5)
2
=IF(COUNTA(B2:D2)=1,B2,IF(COUNTA(B2:D2)=2,B2&","&C2,B2&","&C2&","&D2))
1