software

I need 1.6 from 1.6mm² and 20.23 from 20.23mm²

1) I cannot use RIGHT function to extract as number starts from left.
2) I cannot use LEFT function as number is not fix length
3) I cannot use MID function as num_start refererence is from left not right

Following also some example which difficult to extract
1) 100.2Volts, 15Volts
2) 20m/s, 1.25m/s, 10.0m/s
3) 1.235mØ, 34.0mØ

Hi,

Try using

=LEFT(A1,FIND(quot;mquot;,A1)-1)

To separate the numbers from 100.2Volts, use

=LEFT(A1,FIND(quot;Vquot;,A1)-1)

Regards

Govind.Francis WF Lee wrote:
gt; I need 1.6 from 1.6mm² and 20.23 from 20.23mm²
gt;
gt; 1) I cannot use RIGHT function to extract as number starts from left.
gt; 2) I cannot use LEFT function as number is not fix length
gt; 3) I cannot use MID function as num_start refererence is from left not right
gt;
gt; Following also some example which difficult to extract
gt; 1) 100.2Volts, 15Volts
gt; 2) 20m/s, 1.25m/s, 10.0m/s
gt; 3) 1.235mØ, 34.0mØ

Hi!

Try one of these:

=--LEFT(A1,LEN(A1)-3)

The -3 is for the length of mm2 (3 characters)

=--SUBSTITUTE(A1,quot;mm2quot;,quot;quot;)

Use these for the other examples as well.

Biff

quot;Francis WF Leequot; lt;Francis WF gt; wrote in message
news
gt;I need 1.6 from 1.6mm� and 20.23 from 20.23mm�
gt;
gt; 1) I cannot use RIGHT function to extract as number starts from left.
gt; 2) I cannot use LEFT function as number is not fix length
gt; 3) I cannot use MID function as num_start refererence is from left not
gt; right
gt;
gt; Following also some example which difficult to extract
gt; 1) 100.2Volts, 15Volts
gt; 2) 20m/s, 1.25m/s, 10.0m/s
gt; 3) 1.235m�, 34.0m�
On Sun, 22 Jan 2006 18:21:02 -0800, quot;Francis WF Leequot; lt;Francis WF
gt; wrote:

gt;I need 1.6 from 1.6mm� and 20.23 from 20.23mm�
gt;
gt;1) I cannot use RIGHT function to extract as number starts from left.
gt;2) I cannot use LEFT function as number is not fix length
gt;3) I cannot use MID function as num_start refererence is from left not right
gt;
gt;Following also some example which difficult to extract
gt;1) 100.2Volts, 15Volts
gt;2) 20m/s, 1.25m/s, 10.0m/s
gt;3) 1.235m�, 34.0m�

In your second set, are all the values on each line in one cell?

If so, this seems like a good spot to use quot;regular expressionsquot;.

First, download and install Longre's free morefunc.xll add-in from
xcell05.free.fr

I set up the following data:

A1:1.6mm
A2:20.23mm
A3:100.2Volts, 15Volts
A4:20m/s, 1.25m/s, 10.0m/s
A5:1.235m�, 34.0m�

B1:=REGEX.MID($A1,quot;(\d |\.) quot;,COLUMNS($A:A))

Copy/Drag down to B5. Then select B1:B5 and copy/drag across as far as needed.
In the example you give, that would be three columns.

The quot;COLUMNS()quot; argument returns a '1' in the first column, and increments as
you drag across. This is the quot;indexquot; of the number in the cell in column A.
So, for example:

B4:20
C4:1.25
D4:10.0

The function returns a the number as a string. Where there are fewer values
than the index, it returns an empty string.

To convert the strings to quot;real numbersquot;, precede the function by a double
unary:

=--REGEX.MID($A1,quot;(\.|\d) quot;,COLUMNS($A:A))

However, in the cells with quot;empty stringsquot;, this will return a #VALUE error.
To get rid of that, if it is a problem, you could either use Conditional
Formatting to hide the error, or check for an error in the formula:

=IF(ISERR(-REGEX.MID($A1,quot;(\.|\d) quot;,COLUMNS($A:A))),
quot;quot;,--REGEX.MID($A1,quot;(\.|\d) quot;,COLUMNS($A:A)))--ron

Hi,

You may want to try the following array formula in cell
B14(Ctrl Shift Enter). I assume your text is in A14

=1*MID(A14,MATCH(TRUE,ISNUMBER(1*MID(A14,ROW($1:$2 0),1)),0),COUNT(1*MID(A14,ROW($1:$20),1)) IF(ISNUM BER(MATCH(quot;.quot;,MID(A14,ROW($1:$20),1),0)),1,0))

quot;Francis WF Leequot; wrote:

gt; I need 1.6 from 1.6mm² and 20.23 from 20.23mm²
gt;
gt; 1) I cannot use RIGHT function to extract as number starts from left.
gt; 2) I cannot use LEFT function as number is not fix length
gt; 3) I cannot use MID function as num_start refererence is from left not right
gt;
gt; Following also some example which difficult to extract
gt; 1) 100.2Volts, 15Volts
gt; 2) 20m/s, 1.25m/s, 10.0m/s
gt; 3) 1.235mØ, 34.0mØ

Ron Rosenfeld wrote...
....
gt;B1:=REGEX.MID($A1,quot;(\d |\.) quot;,COLUMNS($A:A))
....

This may work with the OP's examples, but (\d |\.) would return IP
addresses, elipses, etc. If only valid numeric strings should be
returned, then there may be one and only one decimal point.

=REGEX.MID($A1,quot;(\d*\.)?\d quot;,COLUMNS($A:A))

gt;However, in the cells with quot;empty stringsquot;, this will return a #VALUE error.
gt;To get rid of that, if it is a problem, you could either use Conditional
gt;Formatting to hide the error, or check for an error in the formula:
gt;
gt;=IF(ISERR(-REGEX.MID($A1,quot;(\.|\d) quot;,COLUMNS($A:A))),
gt;quot;quot;,--REGEX.MID($A1,quot;(\.|\d) quot;,COLUMNS($A:A)))

Um, why not REGEX.COUNT?

=IF(REGEX.COUNT($A1,quot;(\d*\.)?\d quot;)lt;=COLUMNS($A:A),
--REGEX.MID($A1,quot;(\d*\.)?\d quot;,COLUMNS($A:A)),quot;quot;)

All this said, if the left numeric substring would always be 10
characters or less, it may be expedient to use built-in formulas, e.g.,

=LOOKUP(1E 12,1/MID(A1,1,{1,2,3,4,5,6,7,8,9,10}),--MID(A1,1,{1,2,3,4,5,6,7,8,9,10}))On 22 Jan 2006 23:43:50 -0800, quot;Harlan Grovequot; gt; wrote:

gt;Ron Rosenfeld wrote...
gt;...
gt;gt;B1:=REGEX.MID($A1,quot;(\d |\.) quot;,COLUMNS($A:A))
gt;...
gt;
gt;This may work with the OP's examples, but (\d |\.) would return IP
gt;addresses, elipses, etc. If only valid numeric strings should be
gt;returned, then there may be one and only one decimal point.
gt;
gt;=REGEX.MID($A1,quot;(\d*\.)?\d quot;,COLUMNS($A:A))

I did assume valid data in my response. But I realized what you wrote and was
working on an expression to allow only valid numbers after I posted. But,
although I came up with some expressions that worked, they did not seem elegant
and I was going to work on it more this morning.

What I came up with before going to bed was:

quot;((\d \.?)|(\.\d ))(\d )?quot;

Yours is certainly more concise.
gt;
gt;gt;However, in the cells with quot;empty stringsquot;, this will return a #VALUE error.
gt;gt;To get rid of that, if it is a problem, you could either use Conditional
gt;gt;Formatting to hide the error, or check for an error in the formula:
gt;gt;
gt;gt;=IF(ISERR(-REGEX.MID($A1,quot;(\.|\d) quot;,COLUMNS($A:A))),
gt;gt;quot;quot;,--REGEX.MID($A1,quot;(\.|\d) quot;,COLUMNS($A:A)))
gt;
gt;Um, why not REGEX.COUNT?

Easier to copy/paste the original :-)

gt;
gt;=IF(REGEX.COUNT($A1,quot;(\d*\.)?\d quot;)lt;=COLUMNS($A:A) ,
gt;--REGEX.MID($A1,quot;(\d*\.)?\d quot;,COLUMNS($A:A)),quot;quot;)
gt;
gt;All this said, if the left numeric substring would always be 10
gt;characters or less, it may be expedient to use built-in formulas, e.g.,
gt;
gt;=LOOKUP(1E 12,1/MID(A1,1,{1,2,3,4,5,6,7,8,9,10}),--MID(A1,1,{1,2,3,4,5,6,7,8,9,10}))

As I wrote before, to a man with a hammer, all the world's a nail lt;ggt;.--ron

Hi Francis,

This may be the easiest way:

=xlpEXTRACTNUM(quot;100.2Voltsquot;)

Or:

=xlpEXCLUDENOTNUM(quot;100.2Voltsquot;)xlpEXTRACTNUM and xlpEXCLUDENOTNUM are added to Excel by my Excel add-in,
xlPrecision. See:

PrecisionCalc.com/xlpEXTRACTNUM.html
PrecisionCalc.com/xlpEXCLUDENOTNUM.htmlYou can download the free edition here and use it as long as you wish:

PrecisionCalc.comGood Luck,

Greg Lovern
PrecisionCalc.com
Get Your Numbers Right
quot;Francis WF Leequot; lt;Francis WF gt; wrote in message
news
gt;I need 1.6 from 1.6mm� and 20.23 from 20.23mm�
gt;
gt; 1) I cannot use RIGHT function to extract as number starts from left.
gt; 2) I cannot use LEFT function as number is not fix length
gt; 3) I cannot use MID function as num_start refererence is from left not
gt; right
gt;
gt; Following also some example which difficult to extract
gt; 1) 100.2Volts, 15Volts
gt; 2) 20m/s, 1.25m/s, 10.0m/s
gt; 3) 1.235m�, 34.0m�