Concatenate cells (non-adjacent) asking for separator

[mrwul62]

I would like to be able to select different cells, like A4, B2, C1, E5 and have them concatenated in 1 cells
whereby I am asked for separator to be used, like space, or comma, or semi-colon separated

So, instead of manually adding " " each time in =CONCATENATE(A4, " ", B2, " ", C1, " ", E1) , it would be nice if it would be asked what separator to use and then the concetenation to be performed.

Vainly searched for an add-in doing this.

Any suggestions?


Thanks!

 

[BerndKuerzinger]

Hi mrwul62,

If you are using Excel 2016 (incl. latest updates) there could be an easy solution: a new function called TEXTJOIN.
In your example the function should look like this: =TEXTJOIN(" ", FALSE, A4, B2, C1, E1) .

Please see here for reference: https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

Unfortunately, this function does not exist in earlier Excel versions.

Hope this helps,
Bernd

 

[Fluff]

If you are not using xl2016 have a look at Rick Rothstein' function here
http://www.contextures.com/rickrothsteinexcelvbatext.html

 

[mrwul62]

Many thanks to you both!

Up front: I am using the Dutch Excel 2016 version, latest one I believe: Update could not find any updates.
Jointext seems to be translated to TEKST.COMBINEREN (whereby 'Tekst'=Text and 'Combineren' = combine/join).

Microsoft explains this on https://goo.gl/79h7Hj
Whereby in the example given the formula reads textjoin though... (instead of tekst.combineren)

I found some other examples about 'tekst.combineren' on Internet, but I could not get it working.

Later..
On https://goo.gl/XhVcKf
(somewhere in the middle) I was given to believe that this function could be called on using 'functions'

I then checked the available functions, but it wasn't there.
Probably it is because of that, that I could not get it working.



Maybe one day in the near future it will be added to the Dutch Excel 2016 version...
(or maybe it is only available for the 365 version?)

Thanks again.
=

 

[BerndKuerzinger]

mrwul62,

I am very sorry to hear the function seems not available for you. I am using Office 365 / English so it might indeed only be available in 365 so far - hopefully it'll come to your Excel via update soon.

The link Fluff shared to the user defined function code should be working for you in the meantime if you're ok to use VBA.

All the best,
Bernd

 

[azumi]

Hope this helps, please follow this link for file attachment

https://simpan.ugm.ac.id/s/rJsDNExuKzIZAjW

Azumi

 

[Mike Szczesny]

Can you use something like this? However, in order for my formula to work, you need to do 1 more step. You need place your values together in a range vertically down a column. Evidently this formula does not work when use a range across a row. Assume your range is A1:A4. The formula is =concatenate(transpose(A1:A4&" ,")). Before you hit enter, you need to highlight the transpose(......) part of your formula with your mouse. Hit F9. Remove both { and }. Then you can hit enter.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]a[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]a,b,c,d,[/TD]
[/TR]
</tbody>[/TABLE]