ExcelBanter - Extract numbers with units. Eg. 1.6mmÂ², 20.23mmÂ²

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Extract numbers with units. Eg. 1.6mmÂ², 20.23mmÂ² (https://www.excelbanter.com/excel-worksheet-functions/66748-extract-numbers-units-eg-1-6mm%C2%B2-20-23mm%C2%B2.html)

Extract numbers with units. Eg. 1.6mmÂ², 20.23mmÂ²

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Ã˜

Extract numbers with units. Eg. 1.6mmÂ², 20.23mmÂ²

Hi,

Try using

=LEFT(A1,FIND("m",A1)-1)

To separate the numbers from 100.2Volts, use

=LEFT(A1,FIND("V",A1)-1)

Regards

Govind.

Francis WF Lee wrote:
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Ã˜

Extract numbers with units. Eg. 1.6mm², 20.23mm²

On Sun, 22 Jan 2006 18:21:02 -0800, "Francis WF Lee" <Francis WF
wrote:

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Ø

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

If so, this seems like a good spot to use "regular expressions".

First, download and install Longre's free morefunc.xll add-in from
http://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,"(\d+|\.)+",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 "COLUMNS()" argument returns a '1' in the first column, and increments as
you drag across. This is the "index" 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 "real numbers", precede the function by a double
unary:

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

However, in the cells with "empty strings", 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,"(\.|\d)+",COLUMNS($A:A))),
"",--REGEX.MID($A1,"(\.|\d)+",COLUMNS($A:A)))

--ron

Extract numbers with units. Eg. 1.6mmÂ², 20.23mmÂ²

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(".",MID(A14,ROW($1:$20),1),0)),1,0))

"Francis WF Lee" wrote:

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Ã˜

Extract numbers with units. Eg. 1.6mm², 20.23mm²

Ron Rosenfeld wrote...
....
B1: =REGEX.MID($A1,"(\d+|\.)+",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,"(\d*\.)?\d+",COLUMNS($A:A))

However, in the cells with "empty strings", 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,"(\.|\d)+",COLUMNS($A:A))),
"",--REGEX.MID($A1,"(\.|\d)+",COLUMNS($A:A)))

Um, why not REGEX.COUNT?

=IF(REGEX.COUNT($A1,"(\d*\.)?\d+")<=COLUMNS($A:A),
--REGEX.MID($A1,"(\d*\.)?\d+",COLUMNS($A:A)),"")

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}))

Extract numbers with units. Eg. 1.6mm², 20.23mm²

On 22 Jan 2006 23:43:50 -0800, "Harlan Grove" wrote:

Ron Rosenfeld wrote...
...
B1: =REGEX.MID($A1,"(\d+|\.)+",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,"(\d*\.)?\d+",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:

"((\d+\.?)|(\.\d+))(\d+)?"

Yours is certainly more concise.

However, in the cells with "empty strings", 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,"(\.|\d)+",COLUMNS($A:A))),
"",--REGEX.MID($A1,"(\.|\d)+",COLUMNS($A:A)))

Um, why not REGEX.COUNT?

Easier to copy/paste the original :-)

=IF(REGEX.COUNT($A1,"(\d*\.)?\d+")<=COLUMNS($A:A) ,
--REGEX.MID($A1,"(\d*\.)?\d+",COLUMNS($A:A)),"")

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}))

As I wrote before, to a man with a hammer, all the world's a nail <g.

--ron

Extract numbers with units. Eg. 1.6mm², 20.23mm²