using the search formula

[basb87]

Hi,

Hope somebody can help me with a formula

I would like to search through text to find certain ID numbers.

For example:

CSAZ11871Z
YA6200046Z
YA7300232Z
YW0600782S
DAIC03798Y

The strings I look for always contain 10 characters and always have both numbers and letters in them.

FYI: these numbers are numbers that are being used in email titles. I copied the titles to excel, and I am trying to find all the numbers by determine the position of the first character of the string. From there I can extract the strings myself.

Now I use something like this:

=SEARCH(" ?????????Z",B7)

, but this is not good, because sometimes the strings end with "S" or "Y" instead of "Z".

Can someone please help me?

Thanks!

best regards, Bas

 

[arthurbr]

This should extract the number returning it as a text string =TEXT(NPV(-0.9,,IFERROR(MID(A2,1+LEN(A2)-ROW(OFFSET(A$1,,,LEN(A2))),1)%,"")),"############")
Enter as an array formula with Ctrl+Shift+Enter