Extracting numbers from alphanumeric strings
 

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

Extracting numbers from alphanumeric strings
 

Try going to Data---Text-to-Columns

Click on Fixed Width and move the line between the letters and where the
numbers start. Then click finish.


"Poonam" wrote:

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

Extracting numbers from alphanumeric strings
 

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

Extracting numbers from alphanumeric strings
 

Try this based on the following assumptions:

There is *always* 2 hyphens in the cell and the hyphens are *always* part of
the number string.

The number string *always* starts at the 6th character.

Enter this array formula** :

=MID(A1,6,COUNT(--MID(A1,ROW(INDIRECT("6:100")),1))+2)


--
Biff
Microsoft Excel MVP


"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

Extracting numbers from alphanumeric strings
 

On Fri, 4 Apr 2008 14:41:00 -0700, Poonam
wrote:

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

This will return all the numbers and hyphens beginning with the first digit and
ending with the first character that is not a digit or hyphen:

=TRIM(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789")),MIN(SEARCH(
{"a","b","c","d","e","f","g","h","i","j","k","l"," m","n","o","p","q","r","s","t","u","v","w","x","y" ,"z"},
A1&"abcdefghijklmnopqrstuvwxyz",MIN(SEARCH({0,1,2, 3,4,5,6,7,8,9},
A1&"0123456789"))))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) ))

--ron

Extracting numbers from alphanumeric strings
 

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