Need code to edit text

[djl0525]

I need a macro to:

- Remove the numbers for number bulleted items
- Remove the letters for alpha bulleted items
- Insert two line breaks after each sentence
- Replace → with [space]>[space]
- Remove [space]▼ (replace with nothing)

There can be as many as 20 bullets; they can go beyond E and 7.

---------- BEFORE

1. Start Excel, open E5-D1-PerformanceData from your Excel Chapter 5 folder, and save it as E5-D1-PerformanceReport.
To begin, you will choose a different theme for the document, which will change the font, font color, and fill color in both worksheets.
2. Choose Page Layout→Themes→Themes Themes Icon.
Notice that as you place the mouse over the different themes, you can see a preview of the changes on the worksheet. Modifying the theme means any existing formatting that uses a theme font or a theme color will change to match the new theme. However, if, for example, a cell uses standard colors instead of theme colors, it will not be affected by changing the theme.
3. Choose the Slice theme.
4. Choose Home→Font→Fill Color Fill Color Icon menu button ▼.
You can see the Theme Colors in the Fill Color menu have changed, but do NOT select any of the colors at this time. Now you will adjust two of the colors specifically.
5. Choose Page Layout→Themes→Colors Colors Icon→Customize ColorsÂ….
6. Follow these steps to set the colors for Accent 4 and Accent 5:
Figure showing how to set accent colors
Figure showing how to set accent colors
A Click the color menu button ▼ beside Accent 4.
B Choose Dark Purple, Accent 2, Darker 50%.
C Click the color menu button ▼ beside Accent 5.
D Choose Light Turquoise, Text 2, Darker 25%.
E Type LearnFast in the Name box for the new theme colors and click Save.
The new colors for Accent 4 and 5 are seen in the headings at the top of the worksheet. The new theme colors will now be saved for use in other workbooks.
7. Save the file.

---------- AFTER

Start Excel, open E5-D1-PerformanceData from your Excel Chapter 5 folder, and save it as E5-D1-PerformanceReport.


To begin, you will choose a different theme for the document, which will change the font, font color, and fill color in both worksheets.


Choose Page Layout > Themes > Themes Themes Icon.


Notice that as you place the mouse over the different themes, you can see a preview of the changes on the worksheet.


Modifying the theme means any existing formatting that uses a theme font or a theme color will change to match the new theme.


However, if, for example, a cell uses standard colors instead of theme colors, it will not be affected by changing the theme.


Choose the Slice theme.


Choose Home > Font > Fill Color Fill Color Icon menu button.


You can see the Theme Colors in the Fill Color menu have changed, but do NOT select any of the colors at this time.


Now you will adjust two of the colors specifically.


Choose Page Layout > Themes > Colors Colors Icon > Customize ColorsÂ….


Follow these steps to set the colors for Accent 4 and Accent 5:


Figure showing how to set accent colors


Figure showing how to set accent colors


Click the color menu button beside Accent 4.


Choose Dark Purple, Accent 2, Darker 50%.


Click the color menu button beside Accent 5.


Choose Light Turquoise, Text 2, Darker 25%.


Type LearnFast in the Name box for the new theme colors and click Save.


The new colors for Accent 4 and 5 are seen in the headings at the top of the worksheet.


The new theme colors will now be saved for use in other workbooks.


Save the file.


Any help will be greatly appreciated!!
DJ

 

[oldbrewer]

Peter - I think OP is satisfied....

 

[Peter_SSs]

Peter - I think OP is satisfied....

Possibly, but I was not able to see how the requested points below were resolved with the suggested solution, so I thought it reasonable to seek clarification to see if I could help with that.

- Insert two line breaks after each sentence
- Replace → with [space]>[space]
- Remove [space]▼ (replace with nothing)

Further, if the original sample data is actually each line in a separate cell, I don't see how your formula satisfies the following ...

---------- BEFORE
Figure showing how to set accent colors

---------- AFTER
Figure showing how to set accent colors

 

[oldbrewer]

Peter - you are correct. Maybe the OP was able to do those last things himself after the bullets were sorted. He will come back no doubt if necessary.

 

[Peter_SSs]

Maybe the OP was able to do those last things himself ..

That could be so. Hopefully we will find out.

... after the bullets were sorted.

I'm not convinced that the bullets are sorted as well as they might be (ref the last point/example in my previous post where your formula returns "ure showing how to set accent colors") but again hopefully the OP will clarify.

 

[oldbrewer]

Peter - you are correct again - I did not notice a line did not have a bullet point.