To extract number from string using excel formula

Hi. Before we get going on this one...

Is your forum profile showing the Excel PRODUCT that you need this request to work with? In Excel 2016, you may need helper columns or VBA. Which do you prefer?

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

This array formula returns them one number in each cell. If you want tehm concatendated into a single cell, you will need VBA.

This works with your one, lonely, miserable, isolated, single example. It may not work with your complete dataset, as one sample may not be representative of the whole.

=TRANSPOSE(FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(A1,";","-"),"-","</B><B>")&"</B></A>","//B[.*0=0]")

Array formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

I can not help. I know nothing about VBA. You aked for a formula. That is why I tried to help. It is easy if you have a more modern version of Excel, biut not with Excel 2016.

Please Login or Register  to view this content.

=get_n(A1)

or try Power Query, no formula, no vba

Column1	Result
12345-abcded;34567-mnopq;098776-xyzopq	12345;34567;098776

Please Login or Register  to view this content.

You are welcome