Finding right-most cell.

[gcotterl]

Posted: Tue Sep 27, 2011 1:04 am Post subject: Find right-most
byte

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

My text file has 1 million rows each 1,274 characters long.

How can I find the row having the number 9 in the right-most cell?

For example, how do I find the row indicated with an arrow?

0000000000000000000009999900000
0000000099999000000000000000000
9999000000000000000000000000000
0000000000000000000000000999900 <----------------------
0000000000000099999990000000000
0000000000000000000000000000000
0000000000000000099999900000000

 

[Bruno Campanini]

gcotterl formulated the question :

Posted: Tue Sep 27, 2011 1:04 am Post subject: Find right-most
byte

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

My text file has 1 million rows each 1,274 characters long.

How can I find the row having the number 9 in the right-most cell?

For example, how do I find the row indicated with an arrow?

0000000000000000000009999900000
0000000099999000000000000000000
9999000000000000000000000000000
0000000000000000000000000999900 <----------------------
0000000000000099999990000000000
0000000000000000000000000000000
0000000000000000099999900000000

================================================
Public Sub DetectingRightmost9()
Dim i As Range, MaxRightPos As Long, RowNumber As Long

For Each i In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If InStrRev(i, 9) > MaxRightPos Then
MaxRightPos = InStrRev(i, 9)
RowNumber = i.Row
End If
Next
MsgBox "Position " & MaxRightPos & " at Row " & RowNumber

End Sub
======================================

Bruno

 

[Ron Rosenfeld]

Posted: Tue Sep 27, 2011 1:04 am Post subject: Find right-most
byte

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

My text file has 1 million rows each 1,274 characters long.

How can I find the row having the number 9 in the right-most cell?

For example, how do I find the row indicated with an arrow?

0000000000000000000009999900000
0000000099999000000000000000000
9999000000000000000000000000000
0000000000000000000000000999900 <----------------------
0000000000000099999990000000000
0000000000000000000000000000000
0000000000000000099999900000000

What do you mean by a "row each 1,274 characters long"?

Do you mean that each row has 1,274 cells, each with a single character?

Or do you mean that only one cell in each row is of concern, and each of those cells has 1,274 characters?

Or do you mean something else?

 

[Don Guillett]

Didn't I answer this in another post which you could have modified to
NOT trim

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

 

[gcotterl]

What do you mean by a "row each 1,274 characters long"?

Do you mean that each row has 1,274 cells, each with a single character?

Or do you mean that only one cell in each row is of concern, and each of those cells has 1,274 characters?

Or do you mean something else?- Hide quoted text -

- Show quoted text -

Each row has one and that cell has 1,274 characters.

My question simplified: How can I delete the zeroes after the last 9
in each row?

 

[Ron Rosenfeld]

Each row has one and that cell has 1,274 characters.

My question simplified: How can I delete the zeroes after the last 9
in each row?

Expanding on my answer from your other thread

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

should do it. The "1274" can be any number at least as long as the longest string, and not greater than the maximum number of rows in your worksheet (e.g. 65536 or 1048576 depending on your version of Excel).

What result do you want if there is no "9" in the string? As written, the above formula will return an error. And what version of Excel are you using?

 

[isabelle]

hi,

Sub test1()
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For y = Len(c) To 1 Step -1
If Mid(c, y, 1) = 9 Then Range(c.Address) = "'" & Left(c, y): Exit For
Next
Next
End Sub

 

[Martin Brown]

Each row has one and that cell has 1,274 characters.

My question simplified: How can I delete the zeroes after the last 9
in each row?

Doesn't simplify it.

Closest to doing what you want is where source data is in A
Enter in column B

=FIND("9", A1, C$1) and copy down the full extent of the data

Then in C1 enter =1
And in C2 enter =MAX(IF(ISNUMBER(b1..b999, b1..b999,0)))
And in C3 enter =MATCH(C2, b1:b999,0)
entered as an array formula with crtl-shift

Manually adjust C1 based on the feedback in C2 until C1=C2 or C2=0

What a strange thing to want to do!

Regards,
Martin Brown