Delete rightmost zeroes
 

How can I delete all of the zeroes to the right of the last 9 in each
row?

For example, here are some rows:

0000000000000000000009990000000
0000000099099000000000000000000
9999000000000000000000000000000
0000000000000000000000000099900
0000000000000099999990000000000
0900000000000000000000000000000
0000000000000000099999000000000

This is result I'm looking for:

000000000000000000000999
0000000099099
9999
00000000000000000000000000999
000000000000009999999
09
0000000000000000099999

Then, how do I find the longest row?

Delete rightmost zeroes
 

Sub deleterightzerosSAS() 'assumes TEXT formatting
For Each c In _
Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row)
c.Value = Left(c, InStrRev(c, 9))
If Len(c) lenc Then
maxrow = c.Row
lenc = Len(c)
End If
Next c
MsgBox "max " & lenc & " found at row " & maxrow
End Sub







On Sep 27, 4:28*am, gcotterl wrote:
How can I delete all of the zeroes to the right of the last 9 in each
row?

For example, here are some rows:

0000000000000000000009990000000
0000000099099000000000000000000
9999000000000000000000000000000
0000000000000000000000000099900
0000000000000099999990000000000
0900000000000000000000000000000
0000000000000000099999000000000

This is result I'm looking for:

000000000000000000000999
0000000099099
9999
00000000000000000000000000999
000000000000009999999
09
0000000000000000099999

Then, how do I find the longest row?

Delete rightmost zeroes
 

gcotterl has brought this to us :
How can I delete all of the zeroes to the right of the last 9 in each
row?

For example, here are some rows:

0000000000000000000009990000000
0000000099099000000000000000000
9999000000000000000000000000000
0000000000000000000000000099900
0000000000000099999990000000000
0900000000000000000000000000000
0000000000000000099999000000000

This is result I'm looking for:

000000000000000000000999
0000000099099
9999
00000000000000000000000000999
000000000000009999999
09
0000000000000000099999

Then, how do I find the longest row?

If you want to preserve leftmost zeroes, please modify
Don Guillet's formula as follows:

For Each c In Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row)
c.Value = "'" & Left(c, InStrRev(c, 9))
If Len(c) lenc Then
maxrow = c.Row
lenc = Len(c)
End If
Next c
MsgBox "max " & lenc & " found at row " & maxrow

Bruno

Delete rightmost zeroes
 

On Sep 27, 9:19*am, Bruno Campanini wrote:
gcotterl has brought this to us :









How can I delete all of the zeroes to the right of the last 9 in each
row?

For example, here are some rows:

0000000000000000000009990000000
0000000099099000000000000000000
9999000000000000000000000000000
0000000000000000000000000099900
0000000000000099999990000000000
0900000000000000000000000000000
0000000000000000099999000000000

This is result I'm looking for:

000000000000000000000999
0000000099099
9999
00000000000000000000000000999
000000000000009999999
09
0000000000000000099999

Then, how do I find the longest row?

If you want to preserve leftmost zeroes, please modify
Don Guillet's formula as follows:

For Each c In Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row)
* * c.Value = "'" & Left(c, InStrRev(c, 9))
* * If Len(c) lenc Then
* * * * maxrow = c.Row
* * * * lenc = Len(c)
* * End If
Next c
MsgBox "max " & lenc & " found at row " & maxrow

Bruno

Didn't need that. I thought I qualified mine with
'assumes TEXT formatting

Delete rightmost zeroes
 

On Tue, 27 Sep 2011 02:28:21 -0700 (PDT), gcotterl wrote:

How can I delete all of the zeroes to the right of the last 9 in each
row?

For example, here are some rows:

0000000000000000000009990000000
0000000099099000000000000000000
9999000000000000000000000000000
0000000000000000000000000099900
0000000000000099999990000000000
0900000000000000000000000000000
0000000000000000099999000000000

This is result I'm looking for:

000000000000000000000999
0000000099099
9999
00000000000000000000000000999
000000000000009999999
09
0000000000000000099999



Assuming by "row" you mean "cell"

You can do this with a worksheet formula also:

If your data starts in A1, then:

B2: =LEFT(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:99")),1)="9"),ROW(INDIRECT ("1:99"))))

and fill down as far as required. Just make sure the "99" is at least as long as the longest string in the sequence.



Then, how do I find the longest row?

If your above formula results are in, for example, B1:B7, then:

This formula must be **array-entered**:

=MATCH(MAX(LEN(B1:B7)),LEN(B1:B7),0)

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.