Delete cells with 0 as the tenth digit

[PointerMan]

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?

 

[JBeaucaire]

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

 

[PointerMan]

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

 

[Rick Rothstein]

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?

 

[PointerMan]

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]

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?

 

[Shane Devenshire]

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)

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

=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

 

[Rick Rothstein]

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 Rothstein]

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.

 

[JBeaucaire]

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)

 

[Rick Rothstein]

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.

 

[JBeaucaire]

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/communitie...25cd73b-da80-439a-9f56-35d7f8361b49&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.

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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.