Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2009
    Posts
    8

    Question Unanswered: Displaying Unknown symbols in Excel (Square symbols)

    Hi,

    I am trying to run some data into Excel and the output fields data coming from peoplesoft(I have some fields in peoplesoft like DESCRIPTION, when I enter the description I am using enter key to enter text into another line , So that enter key space is taking as blank).

    So when I run the output into Excel some unknown symbols(Square symbols) are showing in my Excel output file.

    Can any one please help on this.

    Thanks
    - Venkat

  2. #2
    Join Date
    Mar 2009
    Location
    Worthing, UK
    Posts
    16

    Thumbs up

    Hi Venkat

    Here is the fomula to remove carriage return symbol.

    Code:
    =TRIM(SUBSTITUTE(A1,CHAR(10)," "))
    or if you would like to use VBA code

    Code:
    Sub removeCarriageReturn()
    'Remove Carriage Return-  Square Symbol
    'By Muhammad Anisur Rahman
    'Date 28/03/2009
    '==========================================
    
    Dim oldCellValue As String
    
    
    oldCellValue = Range("A2") 'cell value with carriage return symbol
    
    With Application.WorksheetFunction
    
    Range("A2") = .Trim(.Substitute(oldCellValue, Chr(10), " "))
    
    End With
    
    
    End Sub
    Please let me know if it works

    Regards-


    Quote Originally Posted by Venkatdivya
    Hi,

    I am trying to run some data into Excel and the output fields data coming from peoplesoft(I have some fields in peoplesoft like DESCRIPTION, when I enter the description I am using enter key to enter text into another line , So that enter key space is taking as blank).

    So when I run the output into Excel some unknown symbols(Square symbols) are showing in my Excel output file.

    Can any one please help on this.

    Thanks
    - Venkat

  3. #3
    Join Date
    Mar 2009
    Posts
    8
    Thanks for your reply,

    Where I need to apply this formula .. with in Excel or in peoplesoft end.

    I tried the formula in Excel.. but got some formula errors.

    Can please help me ..

  4. #4
    Join Date
    Mar 2009
    Location
    Worthing, UK
    Posts
    16
    use the formulae in Excel
    Code:
    =TRIM(SUBSTITUTE(A1,CHAR(10)," "))
    ERRor?? Could you please explain the error (i.e showing #Name, ~value etc)


    Quote Originally Posted by Venkatdivya
    Thanks for your reply,

    Where I need to apply this formula .. with in Excel or in peoplesoft end.

    I tried the formula in Excel.. but got some formula errors.

    Can please help me ..

  5. #5
    Join Date
    Mar 2009
    Posts
    8
    Thanks for your reply,

    I have special symbols in Excel Column B and i use formula given my you ..

    but no luck for me ..

    still symbols are dispaying in my excel sheet.

    Thanks
    -Venkat

  6. #6
    Join Date
    Mar 2009
    Location
    Worthing, UK
    Posts
    16
    Please upload your sample Excel file, I will have a look or try the VBA code below.

    Code:
    Sub removeCarriageReturn()
    'Remove Carriage Return-  Square Symbol
    'By Muhammad Anisur Rahman
    'Date 28/03/2009
    '==========================================
    
    Dim oldCellValue As String
    
    
    oldCellValue = Range("A2") 'cell value with carriage return symbol
    
    With Application.WorksheetFunction
    
    Range("A2") = .Trim(.Substitute(oldCellValue, Chr(10), " "))
    
    End With
    
    
    End Sub
    Quote Originally Posted by Venkatdivya
    Thanks for your reply,

    I have special symbols in Excel Column B and i use formula given my you ..

    but no luck for me ..

    still symbols are dispaying in my excel sheet.

    Thanks
    -Venkat

  7. #7
    Join Date
    Mar 2009
    Posts
    8
    Here I am sending my excel file as attachment (sending as a zip file).

    Can you please look into this .. please let me know your comments .
    Attached Files Attached Files

  8. #8
    Join Date
    Mar 2009
    Location
    Worthing, UK
    Posts
    16
    Hi

    Use the VBA code below. I have tested and it works fine. It removes all the boxes.

    Cheers

    Code:
    Sub removeCarriageReturnNew()
    
    'Remove Carriage Return-  Square Symbol
    'By Muhammad Anisur Rahman
    'Date 30/03/2009
    '==========================================
    
    Dim oldCellValue As String
    
    
        'Change the Range as required
      oldCellValue = Range("B8") 'You cell value with carriage return symbol with square box
      
      'Change the Range as required
      Range("B9").Value = Replace(oldCellValue, vbCrLf, vbLf) 'remove all square box
      
      
      
    End Sub
    Quote Originally Posted by Venkatdivya
    Here I am sending my excel file as attachment (sending as a zip file).

    Can you please look into this .. please let me know your comments .

  9. #9
    Join Date
    Mar 2009
    Posts
    8
    Thanks for your reply and for testing the same.

    I am not sure how to use the VBA code in my excel..

    Hey sorry to bother you .,. for simple scenario's also..

    Thanks a lot for your help.

    Thanks
    -Venkat
    Last edited by Venkatdivya; 03-30-09 at 11:52.

  10. #10
    Join Date
    Mar 2009
    Location
    Worthing, UK
    Posts
    16
    Hi

    Upload your file, I will write the vba macro for you.

    Regards


    Quote Originally Posted by Venkatdivya
    Thanks for your reply and for testing the same.

    I am not sure how to use the VBA code in my excel..

    Hey sorry to bother you .,. for simple scenario's also..

    Thanks a lot for your help.

    Thanks
    -Venkat

  11. #11
    Join Date
    Mar 2009
    Posts
    8
    Here is my excel file ...

    If you don't mind can you please explain the steps.. where i have to add the VBA code in excel, so that I can handle my self in future.

    Thanks a lot for your help.

    Thanks
    -Venkat
    Attached Files Attached Files
    Last edited by Venkatdivya; 03-30-09 at 15:41.

  12. #12
    Join Date
    Mar 2009
    Posts
    8

    Question

    Hi,

    Can you please write VBA macros for my excel file..

    Thanks a lot

    -Venkat

  13. #13
    Join Date
    Mar 2009
    Posts
    8

    Question

    Please write VBA code for my excel file..or can you please tell me where I need add this VBA code in my excel file.

    this is urgent .

    Please help me on this.

    Thanks
    -Venkat
    Last edited by Venkatdivya; 03-31-09 at 16:44.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •