making sure a cell is empty
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: making sure a cell is empty

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Guest

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Bloomington, MN
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Guest

    Default

    If activecell = "" Then

  5. #5
    Guest

    Default

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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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?

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com