bug with cell in excel

  • Thread starter Thread starter bojan0810
  • Start date Start date
B

bojan0810

Hi all...

I got file from a client, and it is acting weird. I never seen anything like it.

It has unknown space in cell that makes editing nightmare.

It looks like there is space after texting in cell A1, but there isnt any space. If you move cursor to end of that blank space and hit backspace, it will delete last letter.

I have no idea what is wrong or how to fix it.

Here is sample of file, it is huge, but mostly cells are like that. This is first time I see this.

https://dl.dropboxusercontent.com/u/57916703/Test.xlsx
 
Thank you Ron. I didnt tried with clean before...

I made code with Clean as you said, and it works perfectly

Thank you for this
 
Hi Bojan,

Am Sun, 3 Aug 2014 02:34:51 -0700 (PDT) schrieb (e-mail address removed):
I made code with Clean as you said, and it works perfectly
Click to expand...

you can also use TextToColumns:

Columns("A:A").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
FieldInfo:=Array(Array(1, 9), Array(2, 1)),
TrailingMinusNumbers:=True


Regards
Claus B.
 
Hi Bojan,

Am Sun, 3 Aug 2014 11:42:56 +0200 schrieb Claus Busch:
you can also use TextToColumns:
Click to expand...

if column B is not empty better use:
Columns("A:A").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, FieldInfo:=Array(Array(1,
9), _
Array(2, 1), Array(3, 9)), TrailingMinusNumbers:=True


Regards
Claus B.
 
Thank you Claus.

What about this one for whole sheet

Sub Macro1()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
cell.Value = Application.Clean(cell.Value)
End If
Next cell

End Sub
 
Hi Bojan,

Am Sun, 3 Aug 2014 04:53:14 -0700 (PDT) schrieb (e-mail address removed):
Sub Macro1()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
cell.Value = Application.Clean(cell.Value)
End If
Next cell

End Sub
Click to expand...

if possible avoid looping through the cells.

Better try:
ActiveSheet.UsedRange.Replace what:=Chr(9), replacement:=""


Regards
Claus B.
 
Thank worked too.

Thank you Claus.

To be honest, I see first time file like this. No idea how client got to that. Never had problem with that before.
 
Dana ponedjeljak, 4. kolovoza 2014. 22:57:01 UTC+2, korisnik Ron Rosenfeld napisao je:
Oh heck, if you want to use VBA code to do this, just use, on the errant sheet:



Cells.Replace what:=Chr(9), replacement:="", lookat:=xlPart



Although Replace doesn't work from the User interface for the tab character, it seems to work fine using VBA code.
Click to expand...

thanks for this too.
 
Back
Top