Cell alignment gone wrong, now only looks right when I double click the cell

[Ironman]

Hi

Something has caused the text alignment in the cells in Column B to change but I'm not aware that I've changed anything myself.

This is the typical view that I now get. The alignment settings, which are correct with both views, are General > Centre > Wrap Text and although these settings are what I want, the current view isn't.

Cullingworth Rd/Viaduct/ Ling Bob roundabout/ Wilsden/Bents Lane/ Hallas Bridge (09/12/2018)

The image shows the alignment that I had up to today which is perfect (same alignment settings), but I only get this when I double click the cell

Is there a way this can be fixed because going through the 7,000 cells and correcting them will take hours and drive me nuts!

Many thanks!

 

[jasonb75]

It looks like you've made the column a bit wider, possibly by using autofit at some point? Reducing the width should correct those cells but may break others.

 

[Ironman]

Wow, you've sorted it Jason, I'm so relieved, thank you so much. No idea how or when I did that, I've never needed to change the column width.

 

[Ironman]

Hi Jason, although your solution appeared to work, the problem reoccurred with earlier backups I had made days ago when I copied and pasted the same text that had triggered the alignment issue only today. After eliminating recent macros and maximum characters (it didn't do it for a small number of characters, but then it still didn't when I exceeded 256 characters so that was eliminated (not that I could see why that would be the cause anyway).

I then found the true reason for the problem - I had copied and pasted the text from an online entry I made that contained a non-MS character "?" further into the text into Col I (aligned exactly the same as Col B). Clearly Excel cannot cope with smileys in a text cell! As soon as I removed it, everything was back to normal.

However, I don't understand why removing the character would have affected the alignment in Col B (if you look at the image, the character causes the text to be "too much" for the cell) - perhaps somebody can explain?

 

[Dan_W]

I don't have an explanation for why this has happened, but I doubt it is because of the smiley faces. Excel can, in fact, cope with smileys in a text cell - especially those that are copied as text from a webpage. The emoji/emoticon set that we use on our phones/computers, etc were added to the Unicode character set in 2010 (Unicode v6) and to varying degrees of implementation, each product in the Office suite can render the emojis.

So, taking your paragraph above as an example and placing it into the cell - this is the result:

Emojis in cells do not render in colour (but there is a workaround solution to this, if anyone is interested!). The following are the unicode codes for some more smiley faces:

It may be that the smiley face that you copied across was in fact an image, but if that were the case, I would expect that the image would also appear when you pasted it into a worksheet. Without seeing the workbook for myself, I can't think of why it would do this, and sorry that I don't have a solution, but I thought the above point re: smiley faces might be helpful, if for no other reason than you can at least disregard it as being a possible cause...?

 

[Ironman]

That's interesting, thanks Dan. You're correct, the smiley face was an image (you can see it in my post, exactly as in my workbook and clearly in colour - my mistake) because when I right clicked the character in the cell it returned the same font as the other characters (Comic Sans MS) which is clearly incorrect.

 

[Dan_W]

Sorry - when you say you clicked the character in the cell - you mean in the cell in the Excel worksheet? I meant that the source (i. the website) might possibly have been an image.

If you do mean the smiley face in the cell of an Excel worksheet returned the same font as the other characters, that's what I would expect to happen with a unicode character. They're not images - they work just like text, and will take on the same font characteristics and the rest of the text in the cell.

 

[Ironman]

Yes, when I clicked the cell in the excel worksheet containing the smiley included in the online text, the alignment in Column B went haywire - 100% caused by that smiley. The website source was an image. When I deleted the smiley, closed and reopened the workbook it everything was OK. No doubt in my mind that that image was the cause. I still don't understand why Col I content should have affected Col B alignment though. even if the alignment for both columns was the same.

 

[Dan_W]

?‍