How to find the first non-empty cell position field in an Excel column

How to find the first non-space position field in a column in excel

Assuming that you want to find the first non-space position in column A, you can use the following formula:

1, =MATCH(TRUE,A1:A65535"＂",) Press the key combination ctrl shift enter to end the formula.

Among them: A1:A65535""Generate a set of arrays composed of logical symbols TRUE and FALSE based on the non-empty condition, and then use the MATCH function to locate the first position that meets the conditions.

2, =MIN(IF(A1:A65535"＂",ROW(1:65535))) Press the key combination ctrl shift enter to end the formula. Among them: use IF(A1:A65535"＂,ROW(1:65535))) to assign a natural number sequence to the qualified ones, and then use MIN to obtain the first qualified value.

How to extract non-null characters in a column of data in excel

If you need to get the data in the last non-empty cell in a row or column in Excel, you can use the following array formula:

If the data is in the area A1:A100, to get the last non-empty cell value, the formula is:

=INDEX(A1:A100,MAX(ROW(A1:A100)*(A1:A100＂＂)))

If you want to get the last non-empty cell value in the first row, the formula is:

=INDEX(1:1,MAX(COLUMN(1:1)*(1:1＂＂)))

After entering the above formula, press Ctrl Shift Enter to end.

In addition, you can also use the following formula:

1. Find data as numerical value:

=LOOKUP(9E 307,A1:A100)

Note: 9.99999999999999E 307 is the maximum value that Excel allows to be entered in a cell. For convenience of writing, the formula is written as "9E 307" because usually there is no such a large value in the cell. Use the LOOKUP function to find a very large value such as "9E 307" that does not exist in the A1:A100 area. Regardless of whether the values ​​​​in the A1:A100 area are arranged in order, the LOOKUP function will return the content of the last cell containing the value.

Get the relative position of the last cell containing a value:

=MATCH(9E 307,A1:A100)

The formula returns the relative position of the last cell containing a value in the A1:A100 range.

If the range to be searched is one column, such as column A, then the above two formulas are:

=LOOKUP(9E 307,A:A)

=MATCH(9E 307,A:A)

If the range you want to search is one row, such as row 5, just change "A:A" in the formula to "5:5".

2. The data to be searched is text:

If the data is in the area A1:A100, to get the content of the last cell containing text, the formula is:

=LOOKUP(CHAR(65535),A1:A100)

To return the relative position of the last cell containing text in the range, the formula is:

=MATCH(CHAR(65535),A1:A100)

The above is the detailed content of How to find the first non-empty cell position field in an Excel column. For more information, please follow other related articles on the PHP Chinese website!