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.
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.
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.
Now drag the formula down to get digits for the entire data.
You could see we have extracted the number from right for all the values in column A.
Hope this helped.
