Getting Rid of Weird Formatting Symbols in Excel?

[Phoenix22]

Hi,

I've exported data from Access to Excel. The data I've exported is from a combo box in Excel, and at the end of each line, there are weird formatting symbols (a black rectangular box).

When this text is exported to Excel, weird squares show up at the end of each piece of text, as well as a "'" at the beginning of each text. Is there a way I could get rid of these symbols and just have a clean piece of text?

Thank you for your time.

 

[DrBowes]

You can use find and replace under the Edit Menu.

First go into one of the cells and copy the rectangle from the cell, by selecting it in the formula bar. Press Ctrl and C to copy, then Ctrl and V to paste it into the Find and Replace dialog box. Leave the Replace box blank. Then click on replace all.

Then do the same thing, replacing the ' with nothing - assuming there aren't any other apostrophes that you want.

To automate this record this as a macro and run it each time the data is imported.

 

[Phoenix22]

Thanks for your reply, I've already tried that - when I copy the rectangle and paste it in the window, nothing shows up. I think its a formatting symbol?

 

[Zathras]

You could try using the =CLEAN() function and then copy / paste-special / values.

 

[IvanMoala]

You could select your Data and Do a text to column format

OR Run code on it like

Sub Clean_Trim()
'// From Help Files:
'// CLEAN > Removes all nonprintable characters from text.
'// Use CLEAN on text imported from other applications that
'// contains characters that may not print with your
'// operating system.
'// For example, you can use CLEAN to remove some low-level
'// computer code that is frequently at the beginning and end
'// of data files and cannot be printed.

'// TRIM > Removes all spaces from text except for single
'// spaces between words. Use TRIM on text that you have
'// received from another application that may
'// have irregular spacing.

Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CleanTrimRg
    oCell = Func.Clean(Func.Trim(oCell))
Next

End Sub

Ivan F Moala

http://www.xcelfiles.com