Your formula works for taking out known data in a string. However, 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)
macropod wrote:
Hi PoonamAssuming all your strings start with 5 letters and the number strings
04-Apr-08
Hi Poona
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
Cheer
--
macropo
[MVP - Microsoft Word
------------------------
Previous Posts In This Thread:
Extracting numbers from alphanumeric strings
SSHRC856-2004-0033 Bielawsk
SSHRC858-20-0027Nadasd
SSHRC858-04-0001Davelu
SSHRC856-04-010 Blair,
SSHRC421-2005-03 Haggert
SSHRC861-2005-025 Garvi
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 advanc
Poonam
Try going to Data--->Text-to-ColumnsClick on Fixed Width and move the line
Try going to Data--->Text-to-Column
Click on Fixed Width and move the line between the letters and where th
numbers start. Then click finish
:
Hi PoonamAssuming all your strings start with 5 letters and the number strings
Hi Poona
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
Cheer
--
macropo
[MVP - Microsoft Word
------------------------
Re: 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
--
Bif
Microsoft Excel MV
Re: Extracting numbers from alphanumeric strings
On Fri, 4 Apr 2008 14:41:00 -0700, Poonam <
[email protected]
wrote
This will return all the numbers and hyphens beginning with the first digit an
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
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 '{}')
Cheer
--
macropo
[MVP - Microsoft Word
------------------------
Submitted via EggHeadCafe - Software Developer Portal of Choice
A Comparison of Managed Compression Algorithms
http://www.eggheadcafe.com/tutorial...arison-of-managed-compression-algorithms.aspx