Hi Poonam,

Here's a more flexible formula to strip out all leading and trailing non-numerics from a cell:
=MID(A1,MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),"")),MAX(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),""))-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),""))+1)
This is an array formula, so you input it with Ctrl-Shift-Enter, after which it will ben enclosed in a pair of braces (ie '{}').

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"macropod" wrote in message ...
Hi Poonam

Assuming all your strings start with 5 letters and the number strings are always followed by a space:
=MID(A1,6,FIND(" ",A1)-6)
would work for a string in A1.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Poonam" wrote in message ...
SSHRC856-2004-0033 Bielawski
SSHRC858-20-0027Nadasdi
SSHRC858-04-0001Daveluy
SSHRC856-04-010 Blair,H
SSHRC421-2005-03 Haggerty
SSHRC861-2005-025 Garvin

For e.g from above information is in Cell A, I want to have only no. like from ISt 856-2004-0033, Could it possible by signle
line formula.

Thanks in advance

Poonam