@George Many Thanks , Great

Thought I would let everyone know that I needed to find the last position of the house numbers in an address field in access. The only way I could do this was to export a short query to excel and run this formula. I know there is a way to get the excel functions in access but this was much easier. I had addresses that had varying house number lengths and then you throw in the 1st, 2nd, 3rd...St., Ave into the mix within the street name and you got a major problem parsing a very bad formatted address string.

address examples 1234 nw (or NW) 4th St.
12 West St North (or N)
123,456, and 789 Heritage Circle (or Crc)
123 & 456 N 1st. St

I figured out a way to parse off the first and second example real quick with some research (I would give credit, but right now I can't remember where I got the answer). It involved creating a VBA function to accomplish it. That worked great but I came to a problem when we come to the first "," or "and" or "&".

Using this formula in excel found the last number I needed without choosing the street number. Since most house numbers ended with a space " " between them and the direction segment or the street name segment, I added --- &" " after the find formula. Like this: in an array formula
=MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0}&" ",E2,ROW(INDIRECT("1:"&LEN(E2)))),0))
A quick export back to an access table and wala! you have the number.
202 & 206 N Blanche Ave = 9

Now I know I could probably parse the rest of the address in excel but it actually worked real well in Access, so I just decided to do the rest there.

If there is a way to do it all in Access, I couldn't find it or figure it out.

goood, thanks

@Guess You are welcome O(∩_∩)O

That's what I needed =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"));

thank you very much!

@Jon Adams Hats off mate..

@Francis Hi,
Sorry can't help with this.

how to get this to work in powerpivot

@George Hi George,

your formula is may meet my requirement.

I need similar formula to find out digit position in the string listed in "A" row in B row

Required output
8
5
1
4
3
1 3
2
5 5
6
6 1
7
9
9 1
1 6
0
9 3
0 2
7 4
9 3
6 5
5 7
4 9
8 10
2 10
0 8
1 9

.... and here I thought that I knew Excel well. Bravo!!!!!

And here is a formula for finding the position of the last numeric character in a string, but WITHOUT using an array formula:

=MAX(SEARCH(CHAR(9),SUBSTITUTE("0123456789"&A1,{0,1,2,3,4,5,6,7,8,9},CHAR(9),LEN(A1)+10-LEN(SUBSTITUTE("0123456789"&A1,{0,1,2,3,4,5,6,7,8,9},"")))))-10

The number 10 appearing in this formula, are due to the length of the constant string "0123456789", that is concatenated in this formula.

This was exactly what I wanted. Thank you.

The function

1). Formula 1: =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"));

It's absolutely what i needed and extremely cool!

Thanks so much!

Jon