Finding characters in a string

[matpj]

Hi all,

I need some way of locating the characters "D0" in a string and then starting at the "D" going to the right 8 characters.


e.g.

CLOSED - D01234DAF Regression Testing
CLOSED D013456 Solution Management
D09999DAA - Risk Analysis

I want a formula that will retrieve the D01234DAF, D013456, D09999DAA from these three strings.
The "D0" appears in different places, but will always be part of the string.

I'm hoping for functions rather than VBA.
The formula will be placed in an adjacetn column so for each row it will get the "D0" number from the adjacent cells.

thanks in advance,
Matt

Can anyone suggest a formula that might work?

[Dav]

Well to start with you do not always want ot go right 8 digits for you example, I am assuming that there is always a space after your string. and this is what I use to select then end, if there is a space inyour string it will fail

the following would work in b16

=MID(A16,SEARCH("D0",A16),SEARCH(" ",A16,SEARCH("D0",A16))-SEARCH("D0",A16))

regards

Dav

[Avner]

I hope it will help you.
the string is in a1:

=MID(A1,FIND("D0",A1),FIND(" ",MID(A1,FIND("D0",A1),255)))

[Dav]

both solutions will work an are as complicated as each other using 5 functions. find and search are basically the same, but find is case sensitive, so take your choice

Regards

Dav

[matpj]

thanks for that - it works perfectly.
both ways! :-D