Unwanted character, can not search and replace!

H

HAL-9000

New Member
Joined
Dec 18, 2007
Messages
12
Hi

I have a sheet that I often have to edit and import into SQL database.
I have no control over the format of the sheet given to me and it seems that something has changed causing me the following problem.:oops:
When I try to import the Data it’s showing ten times as many items than are actually there.

The problem I think is in a text column. I’ve tested this by using an old column from a previous sheet and pasting this into the new sheet and I can import it ok.

On searching this site I found this post http://www.mrexcel.com/forum/showthread.php?t=127146

I’ve tried a few pointers from this but no luck.
What I seem to have is a rectangular box character but I’m not sure what it is.
I can copy and past it into Notepad as it is, but pasted into Word it seems to be a carriage Return.
So whether it’s an actual charter or not I’m not sure.

Are there any tools out there so I can hover over characters in Excel to see what it is! So at least I can be sure what I’m trying to Search and Replace?

Any ideas most welcome
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
TrimALL Macro (McRichie)
ASCII characters sometimes come over from mainframe extracts. TRIM and CLEAN do not pick up ASCII characters 127 and 160. The procedure below will remove them. It will also remove leading and trailing spaces.
Code: 
Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Do Same with carriage return (Alt-Enter)
'Also Treat CHR 010, as a space (CHR 032)
Selection.Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Select the range you want to work with, then run the macro code.
 
Upvote 0
Thanx Datsmart
Sorry to say that didn't work :(
The box characters are still there! If you run Word and type (Alt) 0010 it will look pretty much like that, or (Alt) 0129 in Excel or Word! Needles to say I've tried searching using that! no joy! :oops:
Many thanx for the feedback
 
Upvote 0
Can you identify what the character actually is eg by using the CODE function in Excel eg:

=CODE(LEFT(A1))

assuming A1 holds these characters and that the first character in the cell is one of these boxes.
 
Upvote 0
RichardSchollar said:
Can you identify what the character actually is eg by using the CODE function in Excel eg:

=CODE(LEFT(A1))

assuming A1 holds these characters and that the first character in the cell is one of these boxes.
Click to expand...
Hi thanx for the reply.
Excel is very low on my CV! so is this correct!
I copied one cell to a A1 new blank sheet.
deleted everything else leaving one offending box character!
Put your code in a cell a few below, it now reads 13 in that cell.

If I hold Alt and type 13 on the pad I get a musical note! ♪
This is driving me nuts..
Thanx for any thoughts!
 
Upvote 0
No that's correct - character 13 is a Carriage Return character, so I would have thought that the macro provided by Datsmart would work. Does pointing the following formula at one of these cells containing many of these characters strip them from the cell?

=CLEAN(A1)
 
Upvote 0
Hi
No " =CLEAN(A1)" doesn't strip out any boxes!
It takes a few seconds to run through the macro provided by Datsmart and I'm sure it's looking through the column as I have to turn text wrap off as it expands after running on that column!

I did try Excel Jeanie but it doesn't show the character when it produces a file. It keeps a space if there is one next to the character but loses the box charter!

Is there not an attach file or image option on this board so I could post a small sample file or image?
For reference I'm using Excel 2002
 
Upvote 0
Image added so you guys can see what I see. Note this is just a paste into a clean sheet to take a snapshot, so that's why its not in CSV!o_O
the%20box%20from%20hell.gif
 
Upvote 0
As far as I am aware, what you have there is a Carriage Return and this results from new lines in Windows text files being a Carriage Return - LineFeed combination (I assume that's what you have in your image, it's just that the LFs are not visible as they are actually placing the each line on a new line). I am really surprised that CLEAN (and the macro from John above) have not removed these.

I would like you to send me a sample of the file (eg with one or two cells' data included) so that I can have a look at it. I have sent you a PM with my email address.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
250
MoonDancer
M
B
  • Question
Paste All from Excel clipboard has inconsistent errors
Replies
1
Views
117
Bumbles
B
C
  • Question
Excel Userform Listbox Search issues
Replies
3
Views
345
Akuini
Akuini
N
  • Solved
Find and Replace VBA from a list in a 2nd sheet
Replies
4
Views
187
Lee_ray
L
E
  • Solved
VBA and special characters
Replies
8
Views
200
MARK858
MARK858

Forum statistics

Threads
1,216,514
Messages
6,131,105
Members
449,618
Latest member
lewismillar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top