BLANK CELLS ARE NOT ACTUALLY BLANK
 

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 F5Specialblanks 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

BLANK CELLS ARE NOT ACTUALLY BLANK
 

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: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

Shilpi wrote:

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 F5Specialblanks 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

BLANK CELLS ARE NOT ACTUALLY BLANK
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Shilpi" wrote:

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 F5Specialblanks 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