+ Reply to Thread
Results 1 to 17 of 17

Extract number from string and add zero

  1. #1
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Extract number from string and add zero

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

  3. #3
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    438

    Re: Extract number from string and add zero

    Maybe

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

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Extract number from string and add zero

    Quote Originally Posted by sandy666 View Post
    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?

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Extract number from string and add zero

    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

  6. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Extract number from string and add zero

    Quote Originally Posted by Root_ View Post
    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.

  7. #7
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    438

    Re: Extract number from string and add zero

    You are welcome.

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Extract number from string and add zero

    Quote Originally Posted by sandy666 View Post
    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.

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Extract number from string and add zero

    Maybe read: PowerQuery
    I don't remeber how to start PQ with Ex2013 but link above describe everything (I hope )

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: Extract number from string and add zero

    Just for fun, another option

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

  11. #11
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Extract number from string and add zero

    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.

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Extract number from string and add zero

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

    and classic note: update profile about Excel version

  13. #13
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Extract number from string and add zero

    Quote Originally Posted by sandy666 View Post
    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.

    Quote Originally Posted by sandy666 View Post
    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!
    Attached Images Attached Images

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Extract number from string and add zero

    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
    Last edited by sandy666; 09-16-2017 at 06:41 PM.

  15. #15
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Extract number from string and add zero

    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: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Jacc; 09-17-2017 at 04:17 AM.

  16. #16
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Extract number from string and add zero

    Haha, this is my private point of view: Think three times (or more) before taking action or you will work senselessly

  17. #17
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Extract number from string and add zero


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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Vba to extract number from string
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-28-2017, 12:24 PM
  2. [SOLVED] How to Extract A Number from String if Text within the String Equals XYZ
    By tyrsfury in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-15-2012, 03:30 PM
  3. [SOLVED] Extract a mainly numeric string (ISSN number) from an arbitrary string
    By Buzzed Aldrin in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-04-2012, 01:49 PM
  4. Extract Number from String Help
    By kobe4ever8 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-31-2011, 02:25 PM
  5. Extract number out of a string
    By mkvassh in forum Excel General
    Replies: 4
    Last Post: 04-07-2010, 05:25 PM
  6. Extract Only Number From A String
    By lehainam in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-26-2006, 06:45 AM
  7. How to extract the Number from a String
    By johnbest in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 12-19-2005, 02:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1