Format string of text as a bullet point list

ChrisM92

New Member
Joined
Nov 4, 2020
Messages
20
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I'm running a daily report for my compliance team to flag any missing Right To Work documents in our system for each candidate due to start working for them to review.

I've got this set up to check the status of each item, and then at the end it creates a concatenated list of everything missing, eg;

[CV]◆[TLD Badge Readings]◆[Signed Deduction Policy]◆[Settled/Pre-Settled]

To try and make it clearer for them and the sales team to read I've set it to add a bullet point icon between each item, however they are requesting that it be formatted as an actual bullet pointed list.

Is there a way to do this with Formula or VBA without going through each row and doing it manually? The list would need to be within a single cell
The size of the list varies and can be anywhere between 1 and 50 items, and I can't just resize the column to make each item move to a new line as it won't line up for every item

1621334130054.png
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,434
Office Version
  1. 365
Platform
  1. Windows
I've got this set up to check the status of each item, and then at the end it creates a concatenated list of everything missing
How are you doing that? (vba or formula)
Either way, couldn't you concatenate with a linefeed as well as your bullet?

If you need more, please provide your current code or formula.
 

ChrisM92

New Member
Joined
Nov 4, 2020
Messages
20
Office Version
  1. 2019
Platform
  1. Windows
How are you doing that? (vba or formula)
Either way, couldn't you concatenate with a linefeed as well as your bullet?

If you need more, please provide your current code or formula.

Whoops sorry, that would of been helpful!

I'm using the below formula to create the list;

=IF(TEXTJOIN("◆",TRUE,G2:BW2)="","[None]",TEXTJOIN("◆",TRUE,G2:BW2))

How do I go about adding a linefeed?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,434
Office Version
  1. 365
Platform
  1. Windows
How do I go about adding a linefeed?
Try this formula and set the column to Wrap Text

Excel Formula:
=IF(TEXTJOIN("◆",TRUE,G2:BW2)="","[None]","◆"&TEXTJOIN(CHAR(10)&"◆",TRUE,G2:BW2))
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,434
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

A little simpler:
Excel Formula:
=IF(CONCAT(G2:BW2)="","[None]","◆"&TEXTJOIN(CHAR(10)&"◆",TRUE,G2:BW2))
 

ChrisM92

New Member
Joined
Nov 4, 2020
Messages
20
Office Version
  1. 2019
Platform
  1. Windows
Try this formula and set the column to Wrap Text

Excel Formula:
=IF(TEXTJOIN("◆",TRUE,G2:BW2)="","[None]","◆"&TEXTJOIN(CHAR(10)&"◆",TRUE,G2:BW2))

Absolutely perfect

Thank you!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,434
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome. Thanks for the follow-up. :)

Did you see the alternative I posted?
 

Forum statistics

Threads
1,136,444
Messages
5,675,897
Members
419,591
Latest member
mersanko

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
Top