Extract Number From Right Till A Character Is Found In Excel

In this post we will see how we could extract number/digit from a cell in excel until a text value is found in excel.

In the below figure you could see we have alphanumeric values in column A that ends with some digit, we generally see these alphanumeric value in serial number, bill receipt etc.

We will see how could we leverage excel formula to extract digits from right till a text value is found.

table showing digits extracted from right till character is found




We will use a combination of text function, ROW and ISNUMBER function in excel to extract digit in a cell from right until a character value is found.

Enter the formula = RIGHT(A1,MATCH(FALSE,ISNUMBER(MID(A1,LEN(A1)+1-ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(A1))),1)+0),0)-1) in cell B1 and press Ctrl+Shift+Enter as it is an array formula.

The formula is looking the values in entire column A and extracting all the numeric values from a cell from right.

excel formula showing how to extract numbers from right till a character is found

As soon as you hit Ctrl+Shift+Enter after entering the formula you will observe the formula is enclosed in braces {}, which denotes it is an array formula.

showing how to enter excel formula to get digits from right

Now drag the formula down to get digits for the entire data.

copy the excel formula down to get all the digits from data




You could see we have extracted the number from right for all the values in column A.

Hope this helped.

Share The Knowledge

Random Posts

Leave a Reply