making sure a cell is empty

[huorsa]

How can I make sure that a cell is empty. I am concatenating several cell separated by "|" symbol and when a cell is emppty the concatenation should be || but sometimes I get | |, I don't know why because the cell looks empty.

I want to avoid the space between the pipes if the cell is empty.

Hugo

 

[Guest]

Give this a look over:

http://www.mrexcel.com/board/viewtopic.php?topic=384&forum=2&5

Cheers

 

[jdeitch]

Perhaps you could just not worry about the cell being empty and remove the spaces from your concatenated string. Assuming your resulting string is in B3, the following would remove all the spaces in it. =SUBSTITUTE(B3," ",""). If the string contains spaces you want to keep and you just want to remove a single space between the two vertical line symbols, try =SUBSTITUTE(B3,"| |","||")

JayD

 

[Guest]

If activecell = "" Then

 

[Guest]

If activecell = "" Then
'your actions
else:
end if

 

[Aladin Akyurek]

On 2002-02-22 17:58, huorsa wrote:
How can I make sure that a cell is empty. I am concatenating several cell separated by "|" symbol and when a cell is emppty the concatenation should be || but sometimes I get | |, I don't know why because the cell looks empty.

I want to avoid the space between the pipes if the cell is empty.

Hugo

Care to post the formula that you use?

 

[Jack in the UK]

Hi

How can I make sure that a cell is empty

Select the cell
Right click
Clear contence

Then do as you need - can be done in range selectionor single cell, EMPTY is not cleaned cell delete or by bush space bar or delet characters

HTH

Rdgs
==========
Jack

 

[huorsa]

On 2002-02-23 00:38, Aladin Akyurek wrote:

On 2002-02-22 17:58, huorsa wrote:
How can I make sure that a cell is empty. I am concatenating several cell separated by "|" symbol and when a cell is emppty the concatenation should be || but sometimes I get | |, I don't know why because the cell looks empty.

I want to avoid the space between the pipes if the cell is empty.

Hugo

Care to post the formula that you use?

Aladin:

I am using this code in VB

acumular2 = ActiveCell.Offset(0, 0) & "|" & ActiveCell.Offset(0, 1) & "|" & ActiveCell.Offset(0, 2) & "|" & ActiveCell.Offset(0, 3) & "|"

but sometimes the cell looks empty, but it isn't (it is like pressing the space bar in the cell, it looks empty because you haven't write any characters, but it is not empty because you press the space bar).

Instead og getting: HI|how||you| I get
HI|how| |you|

I want to avoid the second one.

It is a big range thaat is why I can't check cell by cell and deleted manually.

Hugo

 

[Aladin Akyurek]

On 2002-02-23 11:09, huorsa wrote:

On 2002-02-23 00:38, Aladin Akyurek wrote:

On 2002-02-22 17:58, huorsa wrote:
How can I make sure that a cell is empty. I am concatenating several cell separated by "|" symbol and when a cell is emppty the concatenation should be || but sometimes I get | |, I don't know why because the cell looks empty.

I want to avoid the space between the pipes if the cell is empty.

Hugo

Care to post the formula that you use?

Aladin:

I am using this code in VB

acumular2 = ActiveCell.Offset(0, 0) & "|" & ActiveCell.Offset(0, 1) & "|" & ActiveCell.Offset(0, 2) & "|" & ActiveCell.Offset(0, 3) & "|"

but sometimes the cell looks empty, but it isn't (it is like pressing the space bar in the cell, it looks empty because you haven't write any characters, but it is not empty because you press the space bar).

Instead og getting: HI|how||you| I get
HI|how| |you|

I want to avoid the second one.

It is a big range thaat is why I can't check cell by cell and deleted manually.

Hugo

Hugo,

I don't know VBA. However, I'd suggest adding the TRIM function (if exists, the VBA equivalent of it), before you concat the contents of a cell to that of another.

In the world of formulas, that would look something like:

=TRIM(A1)&"|"&TRIM(B1)&"|"&TRIM(C1)

Aladin

 

[huorsa]

On 2002-02-23 11:23, Aladin Akyurek wrote:

On 2002-02-23 11:09, huorsa wrote:

On 2002-02-23 00:38, Aladin Akyurek wrote:

On 2002-02-22 17:58, huorsa wrote:
How can I make sure that a cell is empty. I am concatenating several cell separated by "|" symbol and when a cell is emppty the concatenation should be || but sometimes I get | |, I don't know why because the cell looks empty.

I want to avoid the space between the pipes if the cell is empty.

Hugo

Care to post the formula that you use?

Aladin:

I am using this code in VB

acumular2 = ActiveCell.Offset(0, 0) & "|" & ActiveCell.Offset(0, 1) & "|" & ActiveCell.Offset(0, 2) & "|" & ActiveCell.Offset(0, 3) & "|"

but sometimes the cell looks empty, but it isn't (it is like pressing the space bar in the cell, it looks empty because you haven't write any characters, but it is not empty because you press the space bar).

Instead og getting: HI|how||you| I get
HI|how| |you|

I want to avoid the second one.

It is a big range thaat is why I can't check cell by cell and deleted manually.

Hugo

Hugo,

I don't know VBA. However, I'd suggest adding the TRIM function (if exists, the VBA equivalent of it), before you concat the contents of a cell to that of another.

In the world of formulas, that would look something like:

=TRIM(A1)&"|"&TRIM(B1)&"|"&TRIM(C1)

Aladin

Can you explain me the use of the trim function.

Thanks,

Hugo