Extracting a number from a text string

[smithgt]

I'm looking fo use Excel to fix a problem with another application and how it sort numbers within a filename.

In Column A, I have the folowing rows of data. I'm looking to extract the number 98, 100 & 102 from each row. The problem I have is that whilst the start (left) position is always the same, the number length could be between two or five digits long.

CF\Subsidiary Rights Statement 98 from 400 to 403.pdf
CF\Subsidiary Rights Statement 100 from 406 to 409.pdf
CF\Subsidiary Rights Statement 102 from 412 to 415.pdf

How can I extact this number? (so I can use it to sort the filenames)

thanks

 

[T. Valko]

I'm looking fo use Excel to fix a problem with another application and how it sort numbers within a filename.

In Column A, I have the folowing rows of data. I'm looking to extract the number 98, 100 & 102 from each row. The problem I have is that whilst the start (left) position is always the same, the number length could be between two or five digits long.



How can I extact this number? (so I can use it to sort the filenames)

thanks

Try this...

=LOOKUP(1E100,--MID(A2,32,{1,2,3,4,5}))

 

[smithgt]

Excellent.....Thank you

Is it possible to caculate the start position (currently 32) as the path may change in length, but the constant is "Subsidiary Rights Statement "

 

[T. Valko]

Excellent.....Thank you

Is it possible to caculate the start position (currently 32) as the path may change in length, but the constant is "Subsidiary Rights Statement "

Try this...

xxxxx CF\Subsidiary Rights Statement 98 from 400 to 403.pdf
Some other stuff CF\Subsidiary Rights Statement 100 from 406 to 409.pdf
Subsidiary Rights Statement 102 from 412 to 415.pdf

=LOOKUP(1E100,--MID(A2,SEARCH("Subsidiary Rights Statement",A2)+28,{1,2,3,4,5}))

The 28 is 27 for the length of the string "Subsidiary Rights Statement" plus 1 for the space.

 

[smithgt]

Thank you again....

 

[T. Valko]

Thank you again....

You're welcome!