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]

let's start by looking at some data with bullets and tell us how you want it to end up

 

[djl0525]

let's start by looking at some data with bullets and tell us how you want it to end up

I am loosely referring to 1., 2., 3., 4., 5., 6., 7., and A, B, C, D and E, above, as bullets. In the cell, it's actually plain text.

The BEFORE and AFTER is showing you how I want it to look.

DJ

 

[oldbrewer]

are we talking real bullets or a cell with the letter 1 so it looks like a bullet


say cell A1 is 1. do this I assume you want to change it to do this
assuming you could have 12. do that you will need to detect where the "." is with the search function

say cell A1 is 1 do the other you will need to use search to detect that "." does not appear in the first 4 characters
so if A1 is 13 do nothing you need to ensure there is no "." and that the space is the third character

can you now write a suitable macro ?

 

[oldbrewer]

col A

1. Do this

Do this

####

row 10

12. Do that

Do that

1 do the other

do the other

13 do nothing

do nothing

A think

think

AB ponder

ponder

####

=IF(MID(A10,2,1)=".",MID(A10,4,99),IF(MID(A10,3,1)=".",MID(A10,5,99),IF(MID(A10,2,1)=" ",MID(A10,3,99),MID(A10,4,99))))

this formula finds the dot or the space and returns the appropriate text

it also works with A and AB as shown

you need to build this logic into your macro - if you must have a macro

<colgroup><col><col><col span="12"></colgroup><tbody>
</tbody>

 

[djl0525]

I can appreciate that this can be done with a formula. No macro required. Thank you for that.

I tried the formula you offered. It only returned a small portion of the cell contents.

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

Do you know how to make it work for the entire cell contents?

DJ

 

[oldbrewer]

my post 5 - it does return entire cell contents UNLESS there are more than 99 characters, if so change the 99 in the formula to 999

 

[djl0525]

Thanks for your help!

 

[oldbrewer]

you are most welcome....

 

[Peter_SSs]

Is your "BEFORE" sample data shown in post 1 ..
- All in a single cell, or
- In 18 different cells in a column, or
- Something else?