Excel Extracting Numbers from a String

I was looking for a way to extract numbers from a string in and excel cell. Years ago, I created a VBA module with regular expression functions which could do this kind of work. However, using VBA tends to decrease the portability of an excel spreadsheets depending upon the policies that might be in place. So I have been trying to use standard functions and formulas to improve my spreadsheet portability. I was searching the internet for a solution to this problem and indeed I found several examples of formulas which extracted numbers but what I eventually found is they left the decimals behind.

After more searching, I concluded that I was going to have to figure it out and write my own formula. I thought I should be able to use array-based formulas to solve the problem and a little while later I came up with this.

=VALUE(TEXTJOIN("",TRUE,FILTER(MID(A2,SEQUENCE(1,LEN(A2)),1),ISNUMBER(VALUE(MID(A2,SEQUENCE(1,LEN(A2)),1)))+(MID(A2,SEQUENCE(1,LEN(A2)),1)="."),"")))
Read more of this post
Design a site like this with WordPress.com
Get started