How to make Excel recognise that the "" is blank?

[PATSYS]

Hi all,

I have a formula in A1:A10 that returns either a value or a "".

The result will be copied paaste special values in another cell, B1;B10.

Then I will copy B1 and paste specia, value and SKIP BLANKS in C1:C10

Problem is that excel pastes all the cells in C1:C10 i.e. it does not consider any cell as blank although there are soe with no value (as a result of the "" in the original formula).

Is there anyway for force excel to treat those as blank in such a way that it will work when I do paste special SKIP BLANKS?

Thanks

 

[PATSYS]

No, at that point C1:C10 aren't empty unless B1:B10 were.

Sorry let me clarify.

Within the context of the previous post by PGC that I replied to, he was referring to those cells that have values "" as a result of the formula.

So wheh I said C1:C10 are empty, I was referring only to those cells that got the "" values from B1:B10 i.e. not all in C1:C10 are empty.

 

[xenou]

I can't get off the thought that it may cut the knot knowing why these cells are an issue...For what reason must they be true blanks rather than empty strings disguised as blanks?

Also, the A,B,C column examples are misleading - how are the six columns involved again, and what are you aiming to accomplish?

 

[PATSYS]

Why do these cells need to be blank, rather than contain empty strings? Perhaps you could say more about your ultimate goal - maybe all the copy and paste isn't needed.

Although, I found JBeaucaire's idea to work for me, if I used it on a column with mixed numbers and "" values that had been copied in place.

Edit - No, I was deleting the "" after pasting them in, not while pasting them...so that's different. But the use of the special cells dialog box did work for this too I guess (Control + G, then Special...)

Hi Xenou,

I was using simplified data in my example above. The real data is like this:

I have data in A6:J131. Some of them text and some are numbers and some are supposed to be numbers but are text because their negative sign is at the right side e.g. 3- instead of -3.

Now I constrcuted a formula in L6, as follows:

=IF(RIGHT(TRIM(A6))<>"-","",SUBSTITUTE(IF(RIGHT(TRIM(A6))="-",RIGHT(TRIM(A6))&A6,A6),"-","",2)+0)

Then I coped this formula across/down all the way to X131.

As you know, this formula will convert text values like 3- to numerc value -3.

My ultimate aim is to have these converted values replace the ones existing in A6:J131 only for those converted cells. The ones that are not converted shold just stay as it is.

So I copied/pastespecial values L6:X131 to Z6:AL131 then copied Z6:AL131 paste special values + skipblanks to A6:J131.

Excel has not skpped anything when I pasted so it does not recognise any of the cells in Z6:AL131 as blank and hence my thread.

Is there any other non VBA way to easily convert all the values in the cells within A6:J131 that have the trailing minus sign to its negative value?

 

[Jonmo1]

Here's the solution to your problem...

Now I constrcuted a formula in L6, as follows:

=IF(RIGHT(TRIM(A6))<>"-","",SUBSTITUTE(IF(RIGHT(TRIM(A6))="-",RIGHT(TRIM(A6))&A6,A6),"-","",2)+0)

Instead of making it return a blank ("") when the number does not have the trailing -, make it return the actual value.

so change the formula to

=IF(RIGHT(TRIM(A6))<>"-",A6,SUBSTITUTE(IF(RIGHT(TRIM(A6))="-",RIGHT(TRIM(A6))&A6,A6),"-","",2)+0)

so now your column of formulas is complete, NO blanks.
It contains all the original values plus converted the numbers with trailing -.
Now you don't need to worry about skipping blanks when you copy/paste special / values.


Hope that helps.

 

[PATSYS]

Here's the solution to your problem...



Instead of making it return a blank ("") when the number does not have the trailing -, make it return the actual value.

so change the formula to

=IF(RIGHT(TRIM(A6))<>"-",A6,SUBSTITUTE(IF(RIGHT(TRIM(A6))="-",RIGHT(TRIM(A6))&A6,A6),"-","",2)+0)

so now your column of numbers is complete, NO blanks.
Now you don't need to worry about skipping blanks when you copy/paste special / values.


Hope that helps.

Hi Jonmo,

Thanks and I might just resort to that.

The shortcoming being that that replaces even those that have formula and makes them values.

 

[Jonmo1]

Hi Jonmo,

Thanks and I might just resort to that.

The shortcoming being that that replaces even those that have formula and makes them values.

Is that a problem?

Isn't the goal to covert numbers with trailing - to real negative numbers?

 

[PATSYS]

Hi all,

Just an observation I want to share.

I copied pasted values the L6:X131 to Z6:AL131 right?

Cell Z6 at this point has no value (as a result of the "")

After that, if I make the formula =ISBLANK(Z6), it says FALSE.

But while in Z6, if I just press F2 and enter, =ISBLANK(Z6) suddenly returns TRUE.

Just an observation..

 

[PATSYS]

Is that a problem?

Isn't the goal to covert numbers with trailing - to real negative numbers?

Hi Jonmo,

Well it might cause a problem, especially some of the cells are formula that are dependent on other cells within that area.

The goal is to convert ONLY the numbers with trailing - to real negative numbers, everything else should stay as it is.

 

[Jonmo1]

Hi all,

Just an observation I want to share.

I copied pasted values the L6:X131 to Z6:AL131 right?

Cell Z6 at this point has no value (as a result of the "")

After that, if I make the formula =ISBLANK(Z6), it says FALSE.

But while in Z6, if I just press F2 and enter, =ISBLANK(Z6) suddenly returns TRUE.

Just an observation..

That's the whole point pgc01 tried to explain....

When a formula returns "" - That is NOT technically BLANK. It's a NULL string.
So the cell's formula is returning an actual VALUE, it's just a NULL STRING VALUE....It is NOT Blank, or Empty.

When you paste special values - it pastes the Value, a NULL STRING. It is a VALUE, NOT A BLANK or Empty..
When you then F2 - Enter, that removes the Null string, and the cell is now Blank or Empty.

 

[pgc01]

The cells that have formula that result to null string is in B1:B10

I copied paste special values B1:B10 to C1:C10.

As this point, do we agree that C1:C10 contains the empty cells?

Hi PATSYS

No, we don't agree.

The formula had as a result a null string. You copied, pasted values, how can the cells be empty?

Maybe the easiest is to do some tests, to show clearly that the cells are not empty.

Write in A1:

=""

Copy, paste values. Since the result of the formula was a null string, after the paste values you should still have a null string in A1.

Let's do some tests:

1 - write in another cell:

=IsBlank(A1)

You'll see the result False, meaning the cell is not empty

2 - write in another cell

=IsText(A1)

You'll see the result True. If the cell were empty you'd get false. The True means A1 has a text value, as expected

3 - write in another cell

=A1+1

You'll see the error value #VALUE!. If the cell was empty you'd see 1.

I think this shows inequivocally that A1 is not empty. It has a value, the type is Text, its length is 0 (Try =LEN(A1)), as we expected. The value in A1 is the same before and after the Paste Values, that's the point of using Paste Values, you are keeping the value.


Because the cells are not empty, the Paste with Skip Blanks will not work. If you want it to work you have to clear the cells first.