Square replace 3

[marcoman44]

In excel I have an imported a report that produced square symbols. The problem is that they cannot be replaced trough Edit/Find/Replace. I think they might be some type of carriage returns from the other report. I cannot do a sort and delete rows becuase of the format of the data. Any help is appreciated.

Thanks,

 

[longhair]

marcoman44,
what did my test code produce? also try adding this:

msgbox(asc(test))

if we can isolate the character into something we can put into a var then we can search for and eliminate it.
regards,
longhair

 

[sawedoff]

Change the formula in the new column to =(CLEAN(A!)*1, then after copying it down, reformat the column to the date format.

Sawedoff

 

[marcoman44]

longhair,

I am not a programmer, how exactly do i enter the macro?

 

[sawedoff]

oops, =CLEAN(A1)*1 Sorry for the typo.

Sawedoff

 

[longhair]

marcoman44,
tools, macro, macros off the toolbar.
create one called test. put the code i posted earlier in it.
then run it. yuo will see that excel reports the asc of the character to be 12.
then all we need to do is:

dim x as int
dim y
y = asc(12)
do until x = 1000 'or however many lines there are
if activecell.value = y then 
Selection.EntireRow.delete
 Else: ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
Loop
hth
regards,
longhair

 

[longhair]

last post should be
marcoman44,
tools, macro, macros off the toolbar.
create one called test. put the code i posted earlier in it.
then run it. yuo will see that excel reports the asc of the character to be 12.
then all we need to do is:

dim x as int
dim y
y = asc(12)
do until x = 1000 'or however many lines there are
if activecell.value = y then
Selection.EntireRow.delete
 Else: ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate 'go down to next line
Loop
[\code]
hth
regards,
longhair

 

[longhair]

marcoman44,
make sure to save your spreadsheet as a copy or just before running the above. you cannot undo changes from a macro.
regards,
longhair

 

[marcoman44]

longhair it does not give me the option to create a macro.

 

[longhair]

marcoman44,
i'm done for the day will check back tomorrow. what version of excel? security settings? are you an admin?
regards,
longhair

 

[sawedoff]

If the CLEAN worked except for messing up your dates, try my formula before going the macro route. It woked on a test file I set up, so it should work on yours.

Sawedoff

 

[SilentAiche]

marcoman44,

Three questions for public consumption (i.e., I'm not assuming the answers will lead ME to a solution ):

1) What program created the file you imported?

2) When the cell with the mystery square is highlighted, what appears in the formula bar?

3) If you copy a blank cell over the mystery cell, does the square character go away?

Good Luck!
Tim


[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[earthandfire]

In addition to SilentAiche's three questions:

4) or 1a) What type of file did you import from?

 

[SilentAiche]

er...

5) or 1b) Is a pre-import solution possible?

Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[marcoman44]

Tim,

The data was exported and sent to us via Excel so I am not sure of the native program. The formula bar just shows the square. I am looking at the code from longhair and I am getting an error but it seems to be the solution. The Clean function did work visually but we I run a Macro to delete rows with no data it ignores the ones that had the square. If I go tothe row and hit delete then the Macro works. This is just goofy.

Thanks Everyone for the input and help, hopefully we can fugure this out.

 

[earthandfire]

One thing that you could try - copy the square, paste it into notepad. Save the Notepad file to c:\Square.txt (easy location). At a command prompt type cd \ and press enter.
Then type debug c:\square.txt
you will now see a - as a prompt
type d 100 and press enter
You will see a lot of hexadecimal numbers on the left of the display and some text (probably with lots of dots on the right)

Make a note of the first row of hexadecimal numbers (that will be more than enough)

At the debug prompt type q and press enter - this will close debug

Then post back here with the list of hexadecimal numbers. Hopefully that will tell us what character is causing the problem.

It would also be useful to know the actual size of the Notepad file (it may be more than 1 byte)

 

[longhair]

marcoman44,
to create the macro you need to enter a name in the 'macro name' field. just use what ever you want then copy and paste this into the area between 'sub <your macro name>()' and 'end sub':

dim x as int
dim y
y = asc(12)
do until x = 1000 'or however many rows there are total
x = x + 1
if activecell.value = y then
Selection.EntireRow.delete
 Else: ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate 'go down to next line
endif
Loop

this will delete the entire row that contains the offending character. if you want to keep the row replace 'Selection.EntireRow.delete' with:

activecell.value = ""

before you do this i would confirm the actual asc character that excel sees there. create a macro like i said above call it something different and put the following in it:

dim test
test = acticell.value
msgbox(asc(test))

once you have the macro(s) created you will need to run them - tools - macro - macros - highlight the one you want to run - click run.
hth
regards,
longhair

 

[TonyJollans]

To enable proper identification of this character, in longhair's code, please use ascw instead of asc.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[longhair]

TonyJollans,
good point - a star for you. although, i'm not sure if absolutely necessary. from asc(12) being returned, from a copy & paste of what was posted, my guess is that this report is from a *nix platform. asc and ascw should return the same data.

regards,
longhair

 

[marcoman44]

longhair,

I am getting an application runtime error.

Here is the code:

Public Sub Test()


Dim x As Integer

Dim y
y = Asc(12)
Do Until x = 50000 'or however many lines there are
If ActiveCell.Value = y Then
Selection.EntireRow.Delete
Else: ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate 'go down to next line
End If
Loop

End Sub


It does loop through and then fails.

 

[TonyJollans]

Thanks for the star, longhair.

That CLEAN has an effect also suggests the character is less than 32 and I agree it may not be necessary but the trouble is we don't really know much for sure.

I must admit to having got a bit lost through the thread - at one point marcoman says he wrote a macro and then, later, that he is not a programmer and how does he enter a macro - so I don't know what to believe.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[