×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Weird Excel Format Problem
3

Weird Excel Format Problem

Weird Excel Format Problem

(OP)
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 Adaptor Assy, FWD

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

RE: Weird Excel Format Problem

Have you tried changing the font of the worksheet?

RE: Weird Excel Format Problem

(OP)
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.

RE: Weird Excel Format Problem

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

RE: Weird Excel Format Problem

(OP)
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/getfile.aspx?folder=a8309ea9-dfa8-475e-ae15-56ea83140feb&file=Excel_Problem.xls

RE: Weird Excel Format Problem

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

RE: Weird Excel Format Problem

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

David

RE: Weird Excel Format Problem

3
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)

RE: Weird Excel Format Problem

Daggumit, forgot to post the code.

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)

RE: Weird Excel Format Problem

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.

CODE

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)

RE: Weird Excel Format Problem

(OP)
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

RE: Weird Excel Format Problem

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources