Home |
Search |
Today's Posts |
#1
|
|||
|
|||
reverse text
column (a) 00092a 01298a 0000000567a 0123 01a I need to use a function or procedure to find the last o in each cell in column(a) Can anyone please help thanks. -- tom mcdonald ------------------------------------------------------------------------ tom mcdonald's Profile: http://www.excelforum.com/member.php...o&userid=24369 View this thread: http://www.excelforum.com/showthread...hreadid=472081 |
#2
|
|||
|
|||
On Fri, 30 Sep 2005 08:14:21 -0500, tom mcdonald
wrote: column (a) 00092a 01298a 0000000567a 0123 01a I need to use a function or procedure to find the last o in each cell in column(a) Can anyone please help thanks. I'm interpreting your request as wanting to find the position of the last 0 in the cell. Assuming your data starts in A1 and you want the zero positions in B1 The following is one procedure. Sub FindLastZero() Dim MyString As String Dim iZeroPos As Integer, icount As Integer Dim rStart As Range Set rStart = Range("a12") Do While rStart.Offset(icount, 0) < "" MyString = StrReverse(rStart.Offset(icount, 0)) If InStr(1, MyString, "0") < 0 Then iZeroPos = WorksheetFunction.Find("0", MyString) iZeroPos = Len(MyString) - iZeroPos + 1 rStart.Offset(icount, 1) = iZeroPos End If icount = icount + 1 Loop End Sub HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
|
|||
|
|||
Ooops, sorry. That first line after the Dim Statements should have
been Set rStart = Range("a1") On Fri, 30 Sep 2005 16:25:45 +0100, Richard Buttrey wrote: On Fri, 30 Sep 2005 08:14:21 -0500, tom mcdonald wrote: column (a) 00092a 01298a 0000000567a 0123 01a I need to use a function or procedure to find the last o in each cell in column(a) Can anyone please help thanks. I'm interpreting your request as wanting to find the position of the last 0 in the cell. Assuming your data starts in A1 and you want the zero positions in B1 The following is one procedure. Sub FindLastZero() Dim MyString As String Dim iZeroPos As Integer, icount As Integer Dim rStart As Range Set rStart = Range("a12") Do While rStart.Offset(icount, 0) < "" MyString = StrReverse(rStart.Offset(icount, 0)) If InStr(1, MyString, "0") < 0 Then iZeroPos = WorksheetFunction.Find("0", MyString) iZeroPos = Len(MyString) - iZeroPos + 1 rStart.Offset(icount, 1) = iZeroPos End If icount = icount + 1 Loop End Sub HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
|
|||
|
|||
tom mcdonald wrote...
column (a) 00092a 01298a 0000000567a 0123 01a I need to use a function or procedure to find the last o in each cell in column(a) Can anyone please help thanks. Define the name seq referring to =ROW(INDIRECT("1:1024")). Then use formulas like =LOOKUP(2,1/(MID(A1,seq,1)="0"),seq) Or skip the defined name and use =LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:1024")),1)="0"),ROW(INDIRE CT("1:1024"))) |
#5
|
|||
|
|||
Hi, Try, =LOOKUP(9.9999999999999E+307,FIND(0,A1,ROW($1:$102 4))) HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=472081 |
#6
|
|||
|
|||
In your example, all the 0's are leading zeroes. Is this true in general?
In other words, can we rule out something like 01290a as a possible value? If all zeroes will be leading 0's, then the most straightforward formula (at least to my way of thinking) is =LEN(A1)-LEN(SUBSTITUTE(A1,"0","")) If all zeroes are not necessarily leading 0's, and you want the position of the last leading zero, then this array formula (entered with CTRL-SHIFT-ENTER) will work: =MIN(IF(MID(A1,ROW($1:$1024),1)<"0",ROW($1:$1024) ))-1 If all zeroes are not necessarily leading, and you really want the position of the last zero, you can use some of the other responses. , "tom mcdonald" wrote: column (a) 00092a 01298a 0000000567a 0123 01a I need to use a function or procedure to find the last o in each cell in column(a) Can anyone please help thanks. -- tom mcdonald ------------------------------------------------------------------------ tom mcdonald's Profile: http://www.excelforum.com/member.php...o&userid=24369 View this thread: http://www.excelforum.com/showthread...hreadid=472081 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Reverse Contents in an Excel cell (Text or a Number) | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |