Extracting numbers from alphanumeric strings

  • Thread starter Thread starter Poonam
  • Start date Start date
P

Poonam

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
 
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.
 
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
 
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)
 
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
Click to expand...

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
 
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(INDIRECT("1:"&LEN(A1)))),"")),MAX(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("1:"&LEN(A1)))),""))-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("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 said:
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 said:
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
Click to expand...
Click to expand...
 
I need to use the number that I extract in a calculation, but
excell returns a zero when I use it in a sumation formula
such as =SUM(A1:A10).
I formated both the MID and the SUM cells as currency.
Here is an example of my data:
Amount: $2,387.88
Here is my formula:
=MID(B480,9,FIND(" ",B480)+2)
Click to expand...

The MID function returns text; it does not matter what the cell format
is. And the SUM function ignores text.

Ass-u-me-ing that MID expression correctly extracts the text
"2,387.88" or "2387.88", you need to convert the result to a number as
follows:

=--MID(B480,9,FIND(" ",B480)+2)

But I would be very surprised if that MID expression really works,
except by coincidence.

The second parameter says that you number starts in the 9th
character. That's plausible.

But the third parameter says that the length of the number (6 or 8, if
my assumption above is correct) is magically determined by the
position (plus 2) of the first blank in the text value of the cell.
So for this example, the first blank must be the 4th or 6th
character. Maybe so; but I wouldn't bet the farm on that.

If you started from scratch -- describing your data and asking how to
extract the numeric string as a number -- we might be able to help you
further.

But if you choose to do that, please start a new thread. It is "poor
practice" to piggyback another thread with a different question, much
less a thread that is more than 2 years old.

Also, if you do choose to start a new thread for more help, for
broader participation, you might want to post using the MS Answers
Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.

It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.
 
Back
Top