first 0

[delmac]

Hi all ihave a column of numbers like
00022230013A
00111110007A
00044440132A and I need to find the first 0 from the right in each number,
can anyone please help. Ta.
--
delmac

[Bob Phillips]

Do you mean find its position in the string?

=FIND("~",SUBSTITUTE(A1,"0","~",LEN(A1)-LEN(SUBSTITUTE(A1,"0",""))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"delmac" <delmac@discussions.microsoft.com> wrote in message
news:2240335F-00C1-47C5-94A6-95EB952044C0@microsoft.com...
> Hi all ihave a column of numbers like
> 00022230013A
> 00111110007A
> 00044440132A and I need to find the first 0 from the right in each number,
> can anyone please help. Ta.
> --
> delmac

[Niek Otten]

=FIND("#",SUBSTITUTE(A1,0,"#",LEN(A1)-LEN(SUBSTITUTE(A1,0,""))))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"delmac" <delmac@discussions.microsoft.com> wrote in message news:2240335F-00C1-47C5-94A6-95EB952044C0@microsoft.com...
| Hi all ihave a column of numbers like
| 00022230013A
| 00111110007A
| 00044440132A and I need to find the first 0 from the right in each number,
| can anyone please help. Ta.
| --
| delmac

[Charlie]

to find the position using VB:

Dim iPos As Long
Dim MyString As String
iPos = InStrRev(MyString, "0")


"delmac" wrote:

> Hi all ihave a column of numbers like
> 00022230013A
> 00111110007A
> 00044440132A and I need to find the first 0 from the right in each number,
> can anyone please help. Ta.
> --
> delmac

[delmac]

tried this and got nothing Charlie, thanks though.
--
delmac


"Charlie" wrote:

> to find the position using VB:
>
> Dim iPos As Long
> Dim MyString As String
> iPos = InStrRev(MyString, "0")
>
>
> "delmac" wrote:
>
> > Hi all ihave a column of numbers like
> > 00022230013A
> > 00111110007A
> > 00044440132A and I need to find the first 0 from the right in each number,
> > can anyone please help. Ta.
> > --
> > delmac