Using ConcatenateX with a "New line" as delimiter...

MHjortkjaer

New Member
Joined
Feb 23, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi all,
Is it possible to insert at new line (Alt-enter / <br> / etc) as a delimiter in a ConcatenateX formula (Measure). I Power BI the formula would be Unichar(xx), but that does not seem to exist in Excel... Any suggestions?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Welcome to Mr Excel :)

I'm not sure about ConcatenateX (never used it) but in a regular formula you would use CHAR(10) in conjunction with 'Wrap Text' formatting.

Hopefully this will do what you need.
 
Upvote 0
Thanks - yeah - but i DAX the Formula Char is not known... Thank you for trying...

Welcome to Mr Excel :)

I'm not sure about ConcatenateX (never used it) but in a regular formula you would use CHAR(10) in conjunction with 'Wrap Text' formatting.

Hopefully this will do what you need.
 
Upvote 0
I think that you might be out of luck then. Other suggestions that I found mentioned Shift Enter while typing the formula, but I can't see that working.
 
Upvote 0
I was trying to do the same thing and just found an easy answer. Basically, put the ending portion of the CONCATENATEX argument in the define Measure for the Pivot Table on a second line by typing a return. For example, put a quotation mark on one line and second quotation mark on the second line before closing the formula with the parentheses.

I found this at the following linke: 5 Ways to Concatenate Data with a Line Break in Excel | How To Excel.

It is also possible to make things a big fancier and add a special character to start each line, roughly simulating bulleted text.
 
Upvote 0
Hi KellerAg92.

Thank you - nice and simple...

Did you have succes on actually doing af bullet-list of the items - when copied to ie Powerpoint will actually result in a bullet-list in a single cell?

/Mads
 
Upvote 0
Hi KellerAg92.

Thank you - nice and simple...

Did you have succes on actually doing af bullet-list of the items - when copied to ie Powerpoint will actually result in a bullet-list in a single cell?

/Mads
I have not tried to take anything out of Excel over the PowerPoint, but I had the same idea. Staying in Excel and in a Pivot table, I added a minus sign ("-") in the concatenate sequence and it roughly serves as a "bullet." I have not been able to find how to force an actual bullet character in (CHAR and UNICODE don't seem to work in DAX). There might be a keyboard sequence to type a bullet ASCII character.

Beyond the listing, I have also been able to add sums of values associated with the text strings (e.g., total sales across the items listed in a cell consistent with the row/column segmentation).
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top