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?