Displaying Unknown symbols in Excel (Square symbols)

Venkatdivya · #1 (**permalink**) 03-27-09, 14:09

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

VBAExpert · #2 (**permalink**) 03-27-09, 21:02

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

Venkatdivya · #3 (**permalink**) 03-30-09, 09:01

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 ..

VBAExpert · #4 (**permalink**) 03-30-09, 09:24

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 ..

Venkatdivya · #5 (**permalink**) 03-30-09, 09:36

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

VBAExpert · #6 (**permalink**) 03-30-09, 09:41

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

Venkatdivya · #7 (**permalink**) 03-30-09, 10:07

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 .

VBAExpert · #8 (**permalink**) 03-30-09, 10:23

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 .

Venkatdivya · #9 (**permalink**) 03-30-09, 10:47

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

VBAExpert · #10 (**permalink**) 03-30-09, 11:53

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

Venkatdivya · #11 (**permalink**) 03-30-09, 12:00

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

Venkatdivya · #12 (**permalink**) 03-30-09, 14:39

Hi,

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

Thanks a lot

-Venkat

Venkatdivya · #13 (**permalink**) 03-31-09, 12:39

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

Internet Services

Web Development

Digital Marketing

Consumer Tech