Complex Bullet POint Addition

[dlafko]

I know how to add bullet points generally to and excel cell. The problem I am having is I already have multiple lines of text in one cell in a giant spreadsheet someone else made.
We want to use bullet point to show where the new line of text is in each cell

And I have not been able to figure out how to do it as amaster group. I know I could go in to each cell put the cursor at the start ofthe first line and do the ALT 7 then manually move to the next line and do thesame thing but I have over 1600 cells that need these adjustments.



ASo you can see in the first examples I manually added 2bullets to the corrections officer 1 and 2 but I don’t want to have to do thatmanually thousands of times. The other 2 don’t have bullets yet.






[TABLE="width: 1387"]
<colgroup><col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="247" style="width: 185pt; mso-width-source: userset; mso-width-alt: 9033;"> <col width="117" style="width: 88pt; mso-width-source: userset; mso-width-alt: 4278;"> <col width="133" style="width: 100pt; mso-width-source: userset; mso-width-alt: 4864;"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4132;"> <col width="45" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;"> <col width="255" style="width: 191pt; mso-width-source: userset; mso-width-alt: 9325;"> <col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4388;"> <col width="124" style="width: 93pt; mso-width-source: userset; mso-width-alt: 4534;"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4132;"> <col width="117" style="width: 88pt; mso-width-source: userset; mso-width-alt: 4278;"> <tbody>[TR]
[TD="width: 77, bgcolor: transparent"]47230[/TD]
[TD="width: 247, bgcolor: transparent"]Corrections Officer 3[/TD]
[TD="width: 117, bgcolor: transparent"]47230[/TD]
[TD="width: 133, bgcolor: transparent"]Corrections[/TD]
[TD="width: 111, bgcolor: transparent"]11/21/2018[/TD]
[TD="width: 113, bgcolor: transparent"]12/12/2018[/TD]
[TD="width: 45, bgcolor: transparent"]x[/TD]
[TD="width: 70, bgcolor: transparent"]x[/TD]
[TD="width: 53, bgcolor: transparent"]x[/TD]
[TD="width: 88, bgcolor: transparent"]x[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 255, bgcolor: transparent"]• Corrections Officer 1
• Corrections Officer 2[/TD]
[TD="width: 120, bgcolor: transparent"]12[/TD]
[TD="width: 124, bgcolor: transparent"]12[/TD]
[TD="width: 113, bgcolor: transparent"]Higher than Unsatisfactory [/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 77, bgcolor: transparent"]22060[/TD]
[TD="width: 247, bgcolor: transparent"]Education Administration Associate[/TD]
[TD="bgcolor: transparent"]93153[/TD]
[TD="width: 133, bgcolor: transparent"]Education[/TD]
[TD="bgcolor: transparent"]11/26/2018[/TD]
[TD="bgcolor: transparent"]12/14/2018[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="width: 255, bgcolor: transparent"]Administrative Assistant 2
Administrative Officer 1[/TD]
[TD="bgcolor: transparent"]12[/TD]
[TD="bgcolor: transparent"]24[/TD]
[TD="width: 113, bgcolor: transparent"]Satisfactory or higher[/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 77, bgcolor: transparent"]47230[/TD]
[TD="width: 247, bgcolor: transparent"]Corrections Officer 3[/TD]
[TD="bgcolor: transparent"]31248[/TD]
[TD="width: 133, bgcolor: transparent"]Corrections[/TD]
[TD="bgcolor: transparent"]11/27/2018[/TD]
[TD="bgcolor: transparent"]12/12/2018[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 255, bgcolor: transparent"]Corrections Officer 1
Corrections Officer 2[/TD]
[TD="bgcolor: transparent"]12[/TD]
[TD="bgcolor: transparent"]12[/TD]
[TD="width: 113, bgcolor: transparent, align: left"]Higher than Unsatisfactory[/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]





Any help would be great.

 

[DRSteele]

Are you saying that the cell with Corrections Officer 1 and Corrections Officer 2 is actually one cell containing a carriage return after the "1"? And that you want to put that little round bullet-point symbol in front of each of the items in that cell?

If that is so, do this.


Create a formula in a column out to the right, say ColP (as I assume your Corrections Officer column is ColL with data in L3):

[FONT=Verdana]=UNICHAR(8226)&LEFT(L3,FIND(UNICHAR(10),L3)-1)&UNICHAR(10)&UNICHAR(8226)&MID(L3,FIND(UNICHAR(10),L3)+1,100)[/FONT]

Apply Wrap Text to your new column (ColP). You can then either keep the new column and use it or copy its contents and paste-as-values back to ColL.


UNICHAR(8226) is the Unicode character that delivers a bullet-point, and UNICHAR(10) is a soft carriage-return. The formula finds that and substitutes bullet-points. This will only work for two items per cell. If you have several Items per cell, we can try PowerQuery or you can try VBA.