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?

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 <gcott...@co.riverside.ca.us> 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?

On Sep 27, 9:19*am, Bruno Campanini <bruno...@libero.it> 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