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

 

[BDeitrick]

Open the file in Excel 2007 or Excel 2010, select the cell, then run this macro:

Sub RightMost9()
Dim myC As Range
Dim myR As Long
Dim myL As Integer
Dim i As Integer

myL = 0
myR = 0
For Each myC In Selection
For i = 1 To Len(myC.Value)
If Mid(myC.Value, i, 1) = "9" Then
If i > myL Then
myL = i
myR = myC.Row
End If
End If
Next i
Next myC
MsgBox "Row " & myR & " has the right-most 9"
End Sub

 

[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?

 

[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