BLANK CELLS ARE NOT ACTUALLY BLANK

[Shilpi]

Hi!
I have a data which were having the leading spaces in them.I removed the
leading spaces using the following formula
=replace(A2,1,find(LEFT(TRIM(A2),2),A2)-1,"")
It worked and my leading spaces are removed....then i pasted it like a value.
Now the problem is that when i am doing F5>Special>blanks then the blank
cells are not highlighting as a blank.
i want to actually delete the blank cells across the rows

Please help me

Thanks in Advance

 

[Dave Peterson]

Another way to remove the leading spaces:
=MID(A2,FIND(LEFT(TRIM(A2),1),A2),99)
(99 is a number large enough for the longest string)

If you wanted to remove leading, trailing and reduce multiple consecutive
internal spaces to a single space, you could use:
=trim(a2)

But if the result of the formula is the "" string, then yep. That cell isn't
empty--even after you convert to values.

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

In code you could do something like:

Option Explicit
Sub testme()
With ActiveSheet
With .cells 'or a specific range: With .Range("D")
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End With
End Sub

 

[Shane Devenshire]

Hi,

Here is another variations:

1. Enter the following formula:
=IF(LEFT(A2)=" ","xxxx"&A2,IF(A2="","xxxx "))

2. Then copy, paste special values.
3. Press Ctrl+H and enter xxxx (space followed by xxxx)
4. Click Replace All.