blank not empty

[daveyc18]

so, i import a csv file into excel....the blank cells aren't actually empty

when i do len(a1) it shows "2." if i do "code(a1)" it shows 32. how do i make these cells truly blank?

 

[tyija1995]

Sounds like you have cells with whitespace in them,

Try going into the "blank" cells and deleting them, are there many cells like this?

 

[daveyc18]

Sounds like you have cells with whitespace in them,

Try going into the "blank" cells and deleting them, are there many cells like this?

there's a bumch of cells like...im trying to look for a formula to clean it up and then copy and paste the same vlaue as values to make them blank....Or some sort of vba code to loop through the range and make them blank

 

[tyija1995]

Coincidentally I made this yesterday

Sub TrimText()
Dim rng As Range
Dim cell As Range
Dim txt As String


Set rng = Selection
    For Each cell In rng
        txt = Application.Trim(cell.Value)
        cell.Value = txt
    Next cell
    
End Sub

Bear in mind it will take a very long time to run if you highlight an entire row, so be sure that your selection is sensible, hope this helps.

 

[Fluff]

How about

Sub daveveyc18()
   With Range("A1", Range("A" & Rows.count).End(xlUp))
      .Value = Evaluate("if({1},trim(clean(" & .Address & ")))")
   End With
End Sub

 

[daveyc18]

How about

Sub daveveyc18()
   With Range("A1", Range("A" & Rows.count).End(xlUp))
      .Value = Evaluate("if({1},trim(clean(" & .Address & ")))")
   End With
End Sub

it worked! how does this work?

i've tried combining trim and clean via Excel, but it didnt seem to do the trick...how's this code different?

 

[Fluff]

It isn't, it's the same as putting =TRIM(CLEAN(A1)) in B1 & copying down.