Extract number from string and add zero

For the purpose of sorting I need to extract a number from a string. Should be an easy snack for someone who knows how to manipulate strings with formulas.
I did solve it myself with array formulas and helper columns but I know you guys can do it way slicker.

Originally Posted by Root_

Maybe

=0&LEFT(TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",200),3),100)),2)

Spot on. Thanks Root!
I have seen these string formulas so many times on the forum but I never learned them. It's about time.

Originally Posted by sandy666

Just for fun, with PowerQuery and simple =BASE(cell,10,3)

Ok... Cool way of adding a zero but the input data is in column A which you are not referencing at all.
Am I missing something?

Originally Posted by sandy666

Col A is source for PQ.

see att. without BASE() function

I added in row 16 new value then refresh PQ table. Try by yourself

This is interesting. I never used Power Query before (hardly any other query either) but I have a feeling I will in a not too distant future. I downloaded PQ and installed but it doesn't show up under COM add-ins. Not sure what to do now.

Just for fun, another option

=0&LEFT(MID(A2,FIND("@",SUBSTITUTE(A2,"-","@",3))+1,LEN(A2)),2)

Uh... ooops! I have Excel 2016 (since just a week ago, will update my profile now), office 365 32 bit. I wonder if I f'ed something up by downloading and installing (I did that before your last post, no worries) cause I get errors on the commands you used for extracting in the query.

Originally Posted by sandy666

Ex2k16 has PowerQuery & PowerPivot built-in.
PowerQuery is under DATA tab as Get&Transform

I know now, hence my wondering if I f'ed it up by downloading and installing PQ.

Originally Posted by sandy666

and classic note: update profile about Excel version

Classic "I think I stated that I will" in my previous post.

First when I tried to edit the query the formulas didn't work and I got a warning message. I ran an update on Excel and now the query functionality is there. I still get the warning message though and after some googling I found this: https://social.technet.microsoft.com...rum=powerquery
The downloadable version is newer than the one that is shipped with Excel 2016 it seems.

Anyway, I learned a ton from this, thanks Sandy!

This is idiotic warning. If you've 2k16 for subscribers and my ver. is 2k16 desktop (not for subscribers) you will get this one. the same with 2k10 / 2k13 / 365. The same will happen if my and your version are the same. I don't know why. This is big mystery of MS. You can do: Close and forget

Create your own PQ query from table and all will be correct, but if I will open yours file I will get the same warning that is why it's idiotic.

edit: I have not found an option to disable it (yet ). To be honest I was not looking too much

Well, new criteria showed up over night. I kind of specified it in the text but not in the sample data (a cardinal sin).
With a slightly clearer brain today I went googling and found this:https://www.mrexcel.com/forum/excel-...haracters.html
I also decided I wanted to end up with 5 characters regardless so I used Sandy's BASE trick.

Now with everything solved I got an existential crisis instead. Is this really what I want?
I don't think so. Even if this formula allows me to sort my PO no's correctly in a table with a helper column (a rather unattractive option to start with), the pdf's generated will still not sort correctly in the folder.
The only sensible thing to do is to use three digits for the running numbers to start with 001, 002, 003 etc.

I am reasonably good with formulas myself but string manipulation has always been my Achilles heel. This session has helped a lot thanks to your inputs.

For future readers, this is what it ended up with before I decided to abort the whole idea:

Formula:

=BASE(TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"-","|",3))+1,LEN(A2)),"_",REPT(" ",LEN(A2))),LEN(A2))),10,5)

''''''''''''