Как выбрать первый номер в ячейке в Excel?

To get the numbers if they are in the beginning of the string we can use this:

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,,"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,,"")))),1)) 

as our base formula, This will find the end of the number and return that as the end of the MID() Function.

There is a lot going on here:

SUBSTITUTE(A2,,"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,,""))) As this part iterates through the numbers it is replacing the last instance of each number with }}}.

The third criterion of SUBSTITUTE is the instance. We find the number of instances with the LEN(A2)-LEN(SUBSTITUTE(A2,,"")). It iterates through the numbers and replaces each one at a time with nothing. It then finds the difference in length of the original string and the new one.

So in the case of A2 when it iterates to the 2 it finds 2 and the outer Substitute replaces the last one with }}}. This is just a temprorary place holder.

The Aggregate function is a multi function function. The 14 tells the funtions we are using the Large() function. The 6 tells the function to ignore errors. This is important in that many of the iteration will not find anything and return an error.

With the 1 at the end it tells the function we want the highest return from the Search function which searches for those temporary }}} that are placed through iteration on the last instance of each number.

So the Aggregate returns the max number found. Which we pass to the length criterion in the Mid function.

So we now have found the number at the front of the string.

So we can multiply two of these together to get the desired output(Any math function will turn the returned string into a number):

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,,"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,,"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,,"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,,"")))),1)) 

One Caveat The Aggregate function was introduced in Excel 2010. It may not work with older versions.

If you have an older version you will need to use this longer formula:

=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,,"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,,""))))),SEARCH("}}}",SUBSTITUTE(A2,,"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,,"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,,"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,,""))))),SEARCH("}}}",SUBSTITUTE(B2,,"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,,""))))))) 

It does roughly the same as the one above accept it must test for the errors first before finding the max.

Assuming the number is always separated from any text by a space, you could use something like this. Say your example has data starting in A2, so your first result in C2 would ordinarily look like:

=A2*B2 

We need to replace the simple cell references with a formula that treats the source cell as a number if it's only a number, but extracts the number if there's also text. So C2 would become:

=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1)) 

If the source cell isn't a number, it finds the first space and returns the characters before it. Technically, what it returns is a text value containing digits, but Excel is smart enough to treat it as a number in calculating the product (no need to nest that inside another function to convert it to a number). And note that if the number is floating point, this will still work.

Caveat: This relies on "clean" source data, i.e., either a numeric entry, or a text entry where it starts with a number which is followed by a space. If there is a cell that doesn't conform to that, you would need to either clean up the data or include some form of error checking. The best solution would be to split the two data columns so that any text is in a separate cell.