1

I have been trying to concatenate two cells but the result is not accurate. I have attached a Sheet if someone could help me.

any help will be greatly appreciated.

I have tried with 2 formulas.

=G2&" "&H2

=TEXTJOIN(char(10),TRUE,IF(F2=A2:A92,C2:C92,""))&" "&TEXTJOIN(char(10),TRUE,IF(F2=A2:A92,(B2:B92*100)&"%",""))

But result is not accurate result should be like

Requirements 100%
Meet Client 0%
Requirements 100%
Process 20%
Configure server 0%

But my results come in that way which is wrong

Meet Client
Requirements
Process
Configure server 0%
100%
20%
0%"

https://docs.google.com/spreadsheets/d/14q2Nh3Q7dtrgKWohllXdKXYSz3kDRKqVLaw9ijPmTdQ/edit?usp=sharing

Valiant
  • 523
  • 7

1 Answers1

4

You could try:

enter image description here

Formula in D1:

=TEXTJOIN(CHAR(10),,FILTERXML("<t><s>"&SUBSTITUTE(A1,CHAR(10),"</s><s>")&"</s></t>","//s")&" "&TEXT(FILTERXML("<t><s>"&SUBSTITUTE(B1,CHAR(10),"</s><s>")&"</s></t>","//s"),"0%"))

Check out this Q&A about how to use FILTERXML() to split data in Excel (2013+).

EDIT:

In case you have a version of Excel prior to Excel 2019 you won't have access to TEXTJOIN() and your best bet would be to create an UDF (User Defined Function) through VBA to mimic the desired behaviour. I threw together a quick example below:

Function JOIN2ARRAYS(x As String, y As String, z As String) As String

Dim arr1 As Variant, arr2 As Variant, i As Long
arr1 = Split(x, z)
arr2 = Split(y, z)

For i = LBound(arr1) To UBound(arr1)
    If JOIN2ARRAYS = "" Then
        JOIN2ARRAYS = arr1(i) & " " & arr2(i)
    Else
        JOIN2ARRAYS = JOIN2ARRAYS & z & arr1(i) & " " & arr2(i)
    End If
Next

End Function

Call this in your sheet with =JOIN2ARRAYS(A1,B1,CHAR(10))

JvdV
  • 41,931
  • 5
  • 24
  • 46
  • 1
    Thank you very very much @JvdV – Valiant Mar 08 '21 at 16:42
  • If it does not bother you can you please share that how to apply this on Microsoft Excel 2016 @JvdV – Valiant Mar 09 '21 at 10:19
  • @Mento, `TEXTJOIN()` is a function only available since Excel 2019 and would require a CSE-entering. In Microsoft365 this is done automatically. For Excel2016 you cannot use the above solution. – JvdV Mar 09 '21 at 10:23
  • Yes i have been tried it at home where it was working fine but in office i have MS 2016 where its not working. I hope there can be other solution as well or should i post new question. – Valiant Mar 09 '21 at 10:26
  • @Mento, it is definately supposed to be a new question but I happen to have Excel2016 at work and threw together a quick example. See the edit above. – JvdV Mar 09 '21 at 10:39
  • 1
    This is marvelous Thank you very much for being so kind. Thank you again @JvdV – Valiant Mar 09 '21 at 10:41
  • Sure i will thanks you. – Valiant Mar 09 '21 at 10:52