Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format cells with 0 as the tenth digit | Excel Worksheet Functions | |||
ROUND() to tens or hundreds (not tenth/hundredths)! | Excel Worksheet Functions | |||
How to delete fist 6 digit of all contents in a column? | Excel Discussion (Misc queries) | |||
In excel, how can i delete 1 digit and the decimal point? | Excel Worksheet Functions | |||
How do I set up a formula in excel that is the tenth root of 7 ve. | Excel Worksheet Functions |