Delete red text within selected cells and add space if there isn't one

[Dunno123]

Hello,

I have found a VBA code on the Internet that deletes all red and size 1 characters within selected cells.

The code looks like this:

Please Login or Register  to view this content.

Could anybody help me change the code so that it adds a space if it deletes something in the middle of the cell (provided there's no space to the left or to the right of the deleted text)?

For example if you look at this picture:

asddf.png

After running the current code, A2 will look like A3. Sometimes I have spaces there, but the color of the space may be set to red and will be deleted too, so A1 might also look like A3 after running the code. In both cases I would like the code to add a space there so that the result would be "word1 word2 ".

Important information:

- the space that I would like to be added should have the same size as the character before the deleted part
- the formatting of the rest of the cell shouldn't be affected

Thank you very much in advance for your help.

[TMS]

You could use " " instead of "", that is, a single space instead of null. Then use Trim to remove excess spaces.

[Dunno123]

Thank you for your answer, but unfortunately I can't do that as Trim will change the formatting of the cell.

[jindon]

Try

Please Login or Register  to view this content.

[Dunno123]

jindon: Thank you for your help, I appreciate it a lot. It works great, but the only problem is that the size of the space is the same as the size of the deleted text, rather than the character before it. Which means if it deletes some text in the middle that has size 1, then the space will be there, but won't be visible.

[jindon]

Then try add one line

Please Login or Register  to view this content.

[Dunno123]

jindon: Thank you so much for your help! It works like a charm.