As shown in the figure below, how to extract the numbers in the cells in the first column?
This problem is tricky, but it should be easier to extract using an Excel function.
formula:
=MAX(IFERROR(--MID(A1,ROW($A$1:$A$50),COLUMN($A$1:$J$1)),))
The explanation is as follows:
As we all know, the mid function is used for extraction, and it is the same here. The only difference from the conventional extraction method is that the array method is used here.
The results extracted using the mid function are as follows (using the content of cell A1 as an example):
=MID(A1,ROW($A$1:$A$50),COLUMN($A$1:$J$1))
The mid function extracts the length of each character from the first character to the right: 1 character, 2 characters, 3 characters. . . . .
In this way, the numbers were successfully extracted.
But the problem comes again, how can the extracted numbers be revealed when they are hidden in the vast data? At this time, you need to use the burden reduction function.
Use directly--
=--MID(A1,ROW($A$1:$A$50),COLUMN($A$1:$J$1))
Let’s look at the picture below:
After the burden is reduced, the text will become an error value and the textual number will become a numerical value.
In fact, it's easier to deal with now, because we have the iferror function, and we just need to turn all the errors into numbers.
Let's see how the magical iferror function handles these errors:
=IFERROR(--MID(A1,ROW($A$1:$A$50),COLUMN($A$1:$J$1)),)
Miraculously, all the errors have become 0.
Looking at these numbers, I found that the number to be extracted is actually the maximum value. Have you thought of it? Have you thought of the function that extracts the maximum value? Yes, it is the max function. I feel suddenly enlightened. Is there anything wrong? ?
=MAX(IFERROR(--MID(A1,ROW($A$1:$A$50),COLUMN($A$1:$J$1)),))
