Delete cells with 0 as the tenth digit
 

How do I delete cells with zero as the tenth digit? I've tried using the
formula =IF(MID(AE2575,10,1)="0",1,AE2575) but it doesn't return the actual
cell value if the tenth digit isn't zero.

Any ideas?

Delete cells with 0 as the tenth digit
 

Your formula does work. There must be something wrong with your data.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"PointerMan" wrote:

How do I delete cells with zero as the tenth digit? I've tried using the
formula =IF(MID(AE2575,10,1)="0",1,AE2575) but it doesn't return the actual
cell value if the tenth digit isn't zero.

Any ideas?

Delete cells with 0 as the tenth digit
 

Here's what the data looks like, and part of that formula worked to
conditional format them. This is all in one cell, by the way...

12-30-08 SHIP 74A350834-2019 37
12-30-08 SHIP 901-069-113-134 3
12-31-08 0 70302-02159-120 83
12-31-08 0 901-069-114-174 4


"JBeaucaire" wrote:

Your formula does work. There must be something wrong with your data.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"PointerMan" wrote:

How do I delete cells with zero as the tenth digit? I've tried using the
formula =IF(MID(AE2575,10,1)="0",1,AE2575) but it doesn't return the actual
cell value if the tenth digit isn't zero.

Any ideas?

Delete cells with 0 as the tenth digit
 

If that is ALL in one cell, then the 10th character is not "0"... it is
"S"... the first "0" in the position I THINK you are referring to is
actually the 74th character in the cell (sentences inside a single cell do
not make separate values... everything in a single one cell is a single
value). Given that this is all in one cell, it is unclear exactly what you
are after (especially given your formula and your subject line). Can you
provide more detail?

--
Rick (MVP - Excel)


"PointerMan" wrote in message
...
Here's what the data looks like, and part of that formula worked to
conditional format them. This is all in one cell, by the way...

12-30-08 SHIP 74A350834-2019 37
12-30-08 SHIP 901-069-113-134 3
12-31-08 0 70302-02159-120 83
12-31-08 0 901-069-114-174 4


"JBeaucaire" wrote:

Your formula does work. There must be something wrong with your data.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"PointerMan" wrote:

How do I delete cells with zero as the tenth digit? I've tried using
the
formula =IF(MID(AE2575,10,1)="0",1,AE2575) but it doesn't return the
actual
cell value if the tenth digit isn't zero.

Any ideas?

Delete cells with 0 as the tenth digit
 

Rick,
The first two lines wouldn't be deleted because the tenth digit isn't zero.
They would stay and the last two lines would be deleted because their tenth
digit is zero.


"Rick Rothstein" wrote:

If that is ALL in one cell, then the 10th character is not "0"... it is
"S"... the first "0" in the position I THINK you are referring to is
actually the 74th character in the cell (sentences inside a single cell do
not make separate values... everything in a single one cell is a single
value). Given that this is all in one cell, it is unclear exactly what you
are after (especially given your formula and your subject line). Can you
provide more detail?

--
Rick (MVP - Excel)


"PointerMan" wrote in message
...
Here's what the data looks like, and part of that formula worked to
conditional format them. This is all in one cell, by the way...

12-30-08 SHIP 74A350834-2019 37
12-30-08 SHIP 901-069-113-134 3
12-31-08 0 70302-02159-120 83
12-31-08 0 901-069-114-174 4


"JBeaucaire" wrote:

Your formula does work. There must be something wrong with your data.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"PointerMan" wrote:

How do I delete cells with zero as the tenth digit? I've tried using
the
formula =IF(MID(AE2575,10,1)="0",1,AE2575) but it doesn't return the
actual
cell value if the tenth digit isn't zero.

Any ideas?

Delete cells with 0 as the tenth digit
 

I don't think you will be able to accomplish this with formula (well, not
without using several helper columns maybe). Can you make use of a VB macro
solution? If so, what cells are (can) your data be in... Column AE only? If
so, what cell range?

--
Rick (MVP - Excel)


"PointerMan" wrote in message
...
Rick,
The first two lines wouldn't be deleted because the tenth digit isn't
zero.
They would stay and the last two lines would be deleted because their
tenth
digit is zero.


"Rick Rothstein" wrote:

If that is ALL in one cell, then the 10th character is not "0"... it is
"S"... the first "0" in the position I THINK you are referring to is
actually the 74th character in the cell (sentences inside a single cell
do
not make separate values... everything in a single one cell is a single
value). Given that this is all in one cell, it is unclear exactly what
you
are after (especially given your formula and your subject line). Can you
provide more detail?

--
Rick (MVP - Excel)


"PointerMan" wrote in message
...
Here's what the data looks like, and part of that formula worked to
conditional format them. This is all in one cell, by the way...

12-30-08 SHIP 74A350834-2019 37
12-30-08 SHIP 901-069-113-134 3
12-31-08 0 70302-02159-120 83
12-31-08 0 901-069-114-174 4


"JBeaucaire" wrote:

Your formula does work. There must be something wrong with your data.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"PointerMan" wrote:

How do I delete cells with zero as the tenth digit? I've tried
using
the
formula =IF(MID(AE2575,10,1)="0",1,AE2575) but it doesn't return the
actual
cell value if the tenth digit isn't zero.

Any ideas?

Delete cells with 0 as the tenth digit
 

So you're saying that all four lines are in one cell, separated by an
Alt+Enter? or just continuous?

And you want to get it to the point where it only displays the first two
lines in a single cell? What is the 1 after "0", for in your formula?

=IF(MID(AE2575,10,1)="0",1,AE2575)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"PointerMan" wrote:

Here's what the data looks like, and part of that formula worked to
conditional format them. This is all in one cell, by the way...

12-30-08 SHIP 74A350834-2019 37
12-30-08 SHIP 901-069-113-134 3
12-31-08 0 70302-02159-120 83
12-31-08 0 901-069-114-174 4


"JBeaucaire" wrote:

Your formula does work. There must be something wrong with your data.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"PointerMan" wrote:

How do I delete cells with zero as the tenth digit? I've tried using the
formula =IF(MID(AE2575,10,1)="0",1,AE2575) but it doesn't return the actual
cell value if the tenth digit isn't zero.

Any ideas?

Delete cells with 0 as the tenth digit
 

On Sun, 8 Feb 2009 16:59:00 -0800, PointerMan
wrote:

Here's what the data looks like, and part of that formula worked to
conditional format them. This is all in one cell, by the way...

12-30-08 SHIP 74A350834-2019 37
12-30-08 SHIP 901-069-113-134 3
12-31-08 0 70302-02159-120 83
12-31-08 0 901-069-114-174 4

If I understand you correctly, the above is in one cell, and you want a formula
which will return those strings that do NOT have a "0" in the tenth place:

12-30-08 SHIP 74A350834-2019 37
12-30-08 SHIP 901-069-113-134 3

Also in one cell.

To do that, you can use a UDF (user defined function).

To enter the function, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Then enter this formula in some cell:

=RegexSub(A1,"(\n|^).{9}0.*","")

Replace A1 with the appropriate cell reference.

Be sure to format the cell to "wrap text".

=============================================
Option Explicit
Function RegexSub(Str As String, SrchFor As String, ReplWith As String, _
Optional CaseSensitive As Boolean = False, _
Optional Gl As Boolean = True, _
Optional ML As Boolean = True) As String
Dim objRegExp As Object
Set objRegExp = CreateObject("vbscript.regexp")
With objRegExp
.Pattern = SrchFor
.IgnoreCase = CaseSensitive
.Global = Gl
.MultiLine = ML
End With
RegexSub = objRegExp.Replace(Str, ReplWith)
End Function
===========================================


--ron

Delete cells with 0 as the tenth digit
 

On Sun, 08 Feb 2009 23:26:32 -0500, Ron Rosenfeld
wrote:

=RegexSub(A1,"(\n|^).{9}0.*","")

Minor change in the formula:

=RegexSub(A1,"(\n|^).{9}0.*(\n|$)","")

(The UDF remains the same, but the pattern in the formula needed a small
change).

--ron

Delete cells with 0 as the tenth digit
 

Going with Ron's UDF idea, here is my UDF offering...

Function DeleteZeroLines(S As String) As String
Dim X As Long
Dim Lines() As String
Lines = Split(S, vbLf)
For X = 0 To UBound(Lines)
If Mid(Lines(X), 10, 1) = "0" Then Lines(X) = ""
Next
DeleteZeroLines = Join(Lines, vbLf)
Do While InStr(DeleteZeroLines, vbLf & vbLf)
DeleteZeroLines = Replace(DeleteZeroLines, vbLf & vbLf, vbLf)
Loop
If Left(DeleteZeroLines, 1) = vbLf Then
DeleteZeroLines = Mid(DeleteZeroLines, 2)
End If
If Right(DeleteZeroLines, 1) = vbLf Then
DeleteZeroLines = Left(DeleteZeroLines, Len(DeleteZeroLines) - 1)
End If
End Function

To install it, press Alt+F11 to go into the VB editor, click Insert/Module
from its menu bar and copy/paste the above function into the code window
that opens up. To use this UDF, just put

=DeleteZeroLines(A1)

in a cell (change the A1 cell reference to the actual cell address that
contains your text) and copy the formula down as needed.

--
Rick (MVP - Excel)


"PointerMan" wrote in message
...
Here's what the data looks like, and part of that formula worked to
conditional format them. This is all in one cell, by the way...

12-30-08 SHIP 74A350834-2019 37
12-30-08 SHIP 901-069-113-134 3
12-31-08 0 70302-02159-120 83
12-31-08 0 901-069-114-174 4


"JBeaucaire" wrote:

Your formula does work. There must be something wrong with your data.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"PointerMan" wrote:

How do I delete cells with zero as the tenth digit? I've tried using
the
formula =IF(MID(AE2575,10,1)="0",1,AE2575) but it doesn't return the
actual
cell value if the tenth digit isn't zero.

Any ideas?

Delete cells with 0 as the tenth digit
 

I think you will have to modify that pattern a little more... it seems to
kill off the internal newline character for this text...

12-30-08 SHIP 74A350834-2019 37
12-30-08 0 901-069-113-134 3
12-31-08 SHIP 70302-02159-120 83
12-31-08 0 901-069-114-174 4

or this text...

12-30-08 0 74A350834-2019 37
12-30-08 SHIP 901-069-113-134 3
12-31-08 0 70302-02159-120 83
12-31-08 SHIP 901-069-114-174 4

Also, might I suggest you use as different first argument name besides
Str... that is also the name of a built in VB function... and while it
causes no problem in your existing code, I think it is a good idea not use
it in it anyway.

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Sun, 08 Feb 2009 23:26:32 -0500, Ron Rosenfeld

wrote:

=RegexSub(A1,"(\n|^).{9}0.*","")

Minor change in the formula:

=RegexSub(A1,"(\n|^).{9}0.*(\n|$)","")

(The UDF remains the same, but the pattern in the formula needed a small
change).

--ron

Delete cells with 0 as the tenth digit
 

Gentlemen, I was working with pointerman on another issue regarding this same
dataset, and I believe what he meant to say is that each on of those line is
in one cell. That's a total of 4 cells.

So if those lines were in cells A1:A4 and this formula was in B1:
=IF(MID(A1,10,1)="0",1,A1)

....and copied down, the first two would return their original value and the
second two would return a 1.

His original formula appear to be working in its original form, at least it
does on the sample data he has provided.

If he REALLY wants the ones without SHIP or PKG flag (has a 0 instead) to
"disappear", I would simply make this following correction to his formula:

=IF(MID(A1,10,1)="0","",A1)
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

Delete cells with 0 as the tenth digit
 

But he went out of his way to say "This is all in one cell, by the way" in a
direct response to one of your earlier posts.

--
Rick (MVP - Excel)


"JBeaucaire" wrote in message
...
Gentlemen, I was working with pointerman on another issue regarding this
same
dataset, and I believe what he meant to say is that each on of those line
is
in one cell. That's a total of 4 cells.

So if those lines were in cells A1:A4 and this formula was in B1:
=IF(MID(A1,10,1)="0",1,A1)

...and copied down, the first two would return their original value and
the
second two would return a 1.

His original formula appear to be working in its original form, at least
it
does on the sample data he has provided.

If he REALLY wants the ones without SHIP or PKG flag (has a 0 instead) to
"disappear", I would simply make this following correction to his formula:

=IF(MID(A1,10,1)="0","",A1)
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

Delete cells with 0 as the tenth digit
 

I know, I've read the whole thread and I see where the error occurred.
Pointerman stopped responding to both threads, so I presume he went to bed or
got off work.

Meanwhile, look at this other thread:
http://www.microsoft.com/communities...1b49&p=1&ntf=0

And note the third message where he clarified the example was for 4 cells.

This is just a slip in the meaning of the words, an unfortunate one, I fear.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Rick Rothstein" wrote:

But he went out of his way to say "This is all in one cell, by the way" in a
direct response to one of your earlier posts.

--
Rick (MVP - Excel)

Delete cells with 0 as the tenth digit
 

On Mon, 9 Feb 2009 00:42:33 -0500, "Rick Rothstein"
wrote:

I think you will have to modify that pattern a little more... it seems to
kill off the internal newline character for this text...

Thanks for pointing that out.

The following seems to work on all the examples:

=RegexSub(A1,"^.{9}0.*$[\n\r]","")

--ron

Delete cells with 0 as the tenth digit
 

On Sun, 08 Feb 2009 23:35:48 -0500, Ron Rosenfeld
wrote:

On Sun, 08 Feb 2009 23:26:32 -0500, Ron Rosenfeld
wrote:

=RegexSub(A1,"(\n|^).{9}0.*","")

Minor change in the formula:

=RegexSub(A1,"(\n|^).{9}0.*(\n|$)","")

(The UDF remains the same, but the pattern in the formula needed a small
change).

--ron

Further change, based on a critique by Rick:

=RegexSub(A1,"^.{9}0.*$[\n\r]","")

--ron

Delete cells with 0 as the tenth digit
 

Okay, I see what you mean... I guess we will have to wait for the OP to come
back on and clarify what he actually wants.

--
Rick (MVP - Excel)


"JBeaucaire" wrote in message
...
I know, I've read the whole thread and I see where the error occurred.
Pointerman stopped responding to both threads, so I presume he went to bed
or
got off work.

Meanwhile, look at this other thread:
http://www.microsoft.com/communities...1b49&p=1&ntf=0

And note the third message where he clarified the example was for 4 cells.

This is just a slip in the meaning of the words, an unfortunate one, I
fear.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Rick Rothstein" wrote:

But he went out of his way to say "This is all in one cell, by the way"
in a
direct response to one of your earlier posts.

--
Rick (MVP - Excel)