Weird Excel Format Problem 3

[rnordquest]

This cell was copied from one Excel sheet to another. The original was all in the font used for "Assy". How did only part of it get changed and more importantly how to change it back? Copy format doesn't work.

Grease ????????Assy, FWD

If I delete the "Adaptor" part and start typing in its place it uses the "Assy" font.

 

[gbam]

Have you tried changing the font of the worksheet?

 

[IRstuff]

When you edit the cell, you can select portions of the contents to apply different formatting

TTFN

FAQ731-376

 

[rnordquest]

A font change to the cell only changes the "Assy" part. The problem is the bad stuff font is the same as the good stuff font. If I change "Adaptor" to a different font and then back to the same font as "Assy" I still have the same problem, i.e. it goes back to the original problem.

 

[zdas04]

Can you upload a part of the sheet to ENGINEERING.com, I think I know what is wrong but I need to see it to be certain.

David

 

[rnordquest]

I sort of anticipated that I would have to do that so here it is. I uploaded a file called Excel Problem.xls and it shows as an attachment but I don't see anything when I preview post to indicate it. Hopefully you'll be able to find it.

OK, what I did was copy the url from the Step 3 Attachment. Don't know why I would have to do that but I'll eventually learn.

Thanks,

Roger

http://files.engineering.com/getfil...475e-ae15-56ea83140feb&file=Excel_Problem.xls

 

[brengine]

That is really odd...

The fix that worked for me was:
1) Copy all the text and paste into NotePad.
(Notice there are a bunch of other characters there after all! Anyway...)
2) Now copy everything in NotePad.
3) Switch back to Excel.
4) Then Edit/Paste Special and choose Values.

Ken

 

[zdas04]

It is strange, none of the things that I thought it could be worked.

David

 

[handleman]

Wanna know what's going on? You have a spreadsheet created in a country outside the US, don't you? Or at least by another OS. I work for a Japan based company, and we see this type of issue all the time. If you have multiple language support installed on your PC, Ken's method won't work. Ken, I'm guessing you see a bunch of gibberish characters in your notepad? It's because Unicode uses 2 bytes per characther, whereas Ascii only uses one.

What you have in your spreadsheet are Unicode characters. It's not a formatting issue. If you select a cell and run the code below, it will put the actual Unicode values and the Ascii values into the debug output pane. Run the macro on a cell containing the "weird" text and the top line (actual Unicode character codes) won't match the second line (ASCII values for that same letter). Run the macro on a cell that contains plain ol' text and the two lines will match.

-handleman, CSWP (The new, easy test)

 

[handleman]

Daggumit, forgot to post the code.

Private Const OFFSET_2 = 65536


Sub FullUnicodeDisplay()
Dim myCharString As String
Dim myCharString2 As String
Dim myString As String
Dim StrLen As Long
Dim myNum As Long

myString = ActiveCell.Text
StrLen = Len(myString)

For i = 1 To StrLen
    myNum = IntegerToUnsigned(AscW(Mid(myString, i, 1)))
    myCharString = myCharString & myNum & " "
    myCharString2 = myCharString2 & Asc(Mid(myString, i, 1)) & " "
Next i

Debug.Print myCharString
Debug.Print myCharString2


End Sub

      Function IntegerToUnsigned(Value As Integer) As Long
        If Value < 0 Then
          IntegerToUnsigned = Value + OFFSET_2
        Else
          IntegerToUnsigned = Value
        End If
      End Function

-handleman, CSWP (The new, easy test)

 

[handleman]

Here's some code that will replace the Unicode characters with their corresponding Ascii values. Just select a bunch of cells and run. It worked on the sample sheet posted for all characters except the funny looking backward comma in cells C26 and C27.

Sub Unicode2Ascii()
Dim myString As String
Dim StrLen As Long
Dim myCell As Range
Dim NewStr As String
Dim i As Long


myString = ActiveCell.Text

For Each myCell In Selection.Cells
    myString = myCell.Text
    StrLen = Len(myString)
    NewStr = ""
    For i = 1 To StrLen
        NewStr = NewStr & Chr(Asc(Mid(myString, i, 1)))
    Next i
    myCell.Value = NewStr
Next myCell
End Sub

-handleman, CSWP (The new, easy test)

 

[rnordquest]

What wise men you are. Yes we're in Japan and it was a hassle for the company to get something set up for us. It must be a two op system problem.

I'll try the code. Thanks,

Roger

 

[rnordquest]

handleman,

The code worked fine but had a problem converting something that ended up displaying as a "?". I added a few lines to detect that and replace it with a space.

Thanks,

Roger