SOLVED

Problems with formating text within a cell.

Copper Contributor
 
Hello. Since recently I have an annoying problem with Excel. I have a desktop computer, using Windows 10 Home Edition 64 bit with Excel 2016:

Since a couple of time, Excel is just re-formating my texts within the cells. I am using my familiar format since 20+ years, so I did no major changes here. Right after I open up an Excel file, it re-formats the content in a second. I have a collection of text-modules and as soon as I underline a word or change it to bold characters, Excel converts all following text into MS Sans Serif Size 10. In the attempt to correct this, after the next opening of the file, Excel just makes it happen again (grrr). The initial format (which I use since ever) is Arial Size 11.  And this unwanted re-formating happens only in cells, in which I did underline or bold-mark a word, the other cells are not affected.

I sifted through several threads and did some tweaking on the registry and the Excel-options, but without any success.

Has someone a solution for this painfull bug? And don't tell me, that might be a feature ;)

I put in a picture with an example.

Thank you in advance, guys.

Bye

Matti

 

13 Replies
Can you attached a small workbook please?

Hi Matti,

 

That could be Font mapping/Font substitution issue, please check the latest post in this thread https://superuser.com/questions/959710/why-is-my-windows-10-system-replacing-system-font-typically-a...

I believe one of our clients are running into this issue quite a bit - I've attached a sample file that has a single cell, that when you bold a word then save, when re-opened the formatting will be messed up.

 

Work arounds I've found - 

1. When opened in 'protected mode' first the formatting is correct (unfortunatly if someone opens it without protected mode first all the formatting is broken again if they save)

2. Reset the formatting of the cell - changing the font&size for the whole cell seems to clear out any of the faulty formatting.

 

I still haven't figured out whats causing this and why protected view changes things...

 

It does not happen for me odd enough. Neither on my Excel 2010, nor my Excel 2016 wrecks the formatting after changing it and saving.
One thing that does stand out however is that the workbooks has acquired a custom style probably due to copying and pasting cells from a different workbook and that the cell in question is formatted using that style.
Try deleting the custom style Normal 2 and then retry the format change and subsequent save.
I've done this and the issue is still there. I've also tried resetting the cells formatting completely, setting the cell style to heading.

Note: The issue won't show itself if the workbook is opened in protected mode, so to replicate you'll need to: open & enable editing, save & reopen, bold a word, then save & reopen
I tested in excel 2013 and it work fine, issue only occurs in 2016
That is precisely what I did (Excel 2016 build 8528.2126) and the formatting sticks on my system.

Yeah, it's sometimes intermittant? I notice it sometimes doesn't trigger if I have other excel documents open.

I recorded a quick video of it happening in a new, clean VM (win 10 16257 & office 1708)

 

https://webmshare.com/play/3yY6q

 

best response confirmed by Jan Karel Pieterse (Silver Contributor)
Solution

Hi Tom,

 

It looks like that bug

===

Fixes or workarounds for recent issues in Excel for Windows - Excel

Font formatting is lost when using rich text in Excel 2016 [WORKAROUND]

Last updated: October 11, 2017

ISSUE

When opening a file in Excel 2016 that contains multiple types of text formatting within the same cell, the formatting may be lost. Examples include having some rich text bolded and others in the same cell not bolded, or having different font colors for different characters within the same cell, such as the example below.

===

https://support.office.com/en-us/article/Fixes-or-workarounds-for-recent-issues-in-Excel-for-Windows...

Hello Tom! Since Office 2007 we have the Office-wide Designs(thmx-files) to unify your Text formatting, your color palette and your effects on Shapes. You can define a design for all files to fit in a corporate design. Different formattings on characters, words, sentences are a „quick and dirty way“, that works with most people, I know. Nevertheless it’s a good thing to deal with texts in word. And it’s another thing to calculate with excel. You did text formatting with excel, which never was meant to be, in my opinion. It’s not a bug, it’s a long time Intention, I’m sorry...🤭

@Eva Vogel  I recently migrated to Office 365 after decades of "locally installed." I have mixed font styles (bold, underscore, italic, normal, colors) within spreadsheet cells for years. This sort of flexibility is essential when presenting complex information to senior and/or sophisticated clients. <-For example, pretend this is a spreadsheet legend. Providing styles which apply to entire sheets is fine, but that feature "exchange" eliminating flexibility within cells is choosing style over substance. I am sorely disappointed in the change and your MS response. I find it difficult to believe that this sort of change was socialized with the "power use" community. I certainly would have raised a red flag.

Hi @Vecrumba, you can always say your complaints on Microsoft’s „UserVoice“. And you can find it in your home tab in excel when clicking on the smiley Icon on the upper right of your desktop app. And you can always define your own individual styles on formatting each cell if you want. You only have to save it within a „Office Design“. We all have to learn the new routines and I decided to adjust, not to block myself. Feeling enerved or even disgruntled makes no sense at all, I think and you deserve better, on your behalf. So I hope you will have a nice sunday. Greets, Eva
I have just encountered the issue in Excel 2021 v2304 Build 16327.20248, and found out the way to solve the issue is to *not* use "Accounting" number format for cell formating, instead use other number formating like General or Text.
1 best response

Accepted Solutions
best response confirmed by Jan Karel Pieterse (Silver Contributor)
Solution

Hi Tom,

 

It looks like that bug

===

Fixes or workarounds for recent issues in Excel for Windows - Excel

Font formatting is lost when using rich text in Excel 2016 [WORKAROUND]

Last updated: October 11, 2017

ISSUE

When opening a file in Excel 2016 that contains multiple types of text formatting within the same cell, the formatting may be lost. Examples include having some rich text bolded and others in the same cell not bolded, or having different font colors for different characters within the same cell, such as the example below.

===

https://support.office.com/en-us/article/Fixes-or-workarounds-for-recent-issues-in-Excel-for-Windows...

View solution in original post