reverse text

[tom mcdonald]

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.

[Richard Buttrey]

On Fri, 30 Sep 2005 08:14:21 -0500, tom mcdonald
<[email protected]> 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]

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
<[email protected]> wrote:

>On Fri, 30 Sep 2005 08:14:21 -0500, tom mcdonald
><[email protected]> 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
__________________________

[Harlan Grove]

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(INDIRECT("1:1024")))

[Krishnakumar]

Hi,

Try,

=LOOKUP(9.9999999999999E+307,FIND(0,A1,ROW($1:$1024)))

HTH

[Ron Moore]

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