+ Reply to Thread
Results 1 to 13 of 13

Formula to extract largest number from a string for Politics Dissertation

  1. #1
    Registered User
    Join Date
    05-29-2019
    Location
    London, England
    MS-Off Ver
    Excel (Office) for Mac 2019
    Posts
    3

    Cool Formula to extract largest number from a string for Politics Dissertation

    Hi there, I am a student working on a political project analysing donations to politicians for my dissertation. I would really appreciate your help with this!

    In some cells (in the B column) I have a short paragraph of text such as the following:

    Name of donor: Government of GibraltarAddress of donor: 6 Convent Place, Gibraltar GX11 1AAEstimate of the probable value (or amount of any donation): Flight £251, meals £120 and hotel accommodation with a value of £258; total value £629 Destination of visit: Gibraltar Dates of visit: 16-18 May 2018Purpose of visit: Fact finding mission to assess impact of Brexit on Gibraltar.(Registered 24 May 2018)


    I am looking for a formula to extract the Total Value of the donation so essentially I was trying to come up with a formula that pulls out the largest currency value (i.e. the largest number preceded by a '£' sign. Any help you might have would be hugely appreciated.

    As a note of caution, other cells might only have one currency value, so ideally the formula would look for all instances of '£XXX' and return the largest, or if there is only one instance simply return that.

    I managed to put together a formula for cases with only one instance: =MID(B400,FIND("£",B400),FIND(" ",B400&" ",FIND("£",B400))-FIND("£",B400))+0 but cannot work out how to do it for the other cases.
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula to extract largest number from a string for Politics Dissertation

    I have solved this with VBA code that creates a user-defined function (UDF) using regexp pattern matching. I find that much easier than complex pattern matching using formulas, but not everyone is familiar with VBA. In this file, you use the function LargestAmount which takes the string and returns the largest number with a £ sign.
    Attached Files Attached Files
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Formula to extract largest number from a string for Politics Dissertation

    I agree, formula would be too cumbersome.

    Here's alternate method using Get & Transform (PowerQuery).

    Load data to PQ and then go into editor.

    Add custom column:
    Please Login or Register  to view this content.
    Add another custom column:
    Please Login or Register  to view this content.
    This will generate list that has "£" preceding numbers. Expand column and each value to new row.

    Add another custom column:
    Please Login or Register  to view this content.
    This will extract numbers and "." only from string.

    Change data type to decimal number.

    Select Donation Description column and Group By -> Choose MAX of Custom.2

    Load back to sheet and apply formatting. See attached.
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Formula to extract largest number from a string for Politics Dissertation

    The methods already suggested will be more practical, but thought I'd give it a go with a formula anyway.

    Array confirmed with Shift Ctrl Enter.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Max donation = £999,999.99 (with decimal) or £99,999,999 (no decimal), total of 11 characters. It can go higher by changing the column range at the end, but it starts getting very slow.

  5. #5
    Registered User
    Join Date
    05-29-2019
    Location
    London, England
    MS-Off Ver
    Excel (Office) for Mac 2019
    Posts
    3

    Re: Formula to extract largest number from a string for Politics Dissertation

    Hi Jeff,

    Thank you so much for your response. That definitely sounds like a far better method, however, when I download the file attached there don't seem to be any macros? Perhaps it is an issue with when you saved it? Alternatively might it be an issue that I am on a Mac? Apologies, I probably should have made that clear in my OP.

    Cheers,

    Harry
    Last edited by harryspooner; 05-30-2019 at 05:32 PM.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Formula to extract largest number from a string for Politics Dissertation

    Jeff's suggestion was a UDF not a macro, both are written in vba but you don't run a UDF so you won't see it if you go to view > macros on the ribbon.

    A UDF works like a worksheet function, in that you enter it in a cell as a formula, I haven't downloaded Jeff's updated file, but from his post it looks like you just need to enter

    =LargestAmount(A2)

    Then fill down as you would with any other formula.

    Note that you do have 2 other suggestions to try as well, not sure if PQ is an option with excel for mac without consulting google, but fairly sure you should be ok.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula to extract largest number from a string for Politics Dissertation

    Quote Originally Posted by jason.b75 View Post
    [...]you don't run a UDF so you won't see it if you go to view > macros on the ribbon.

    A UDF works like a worksheet function, in that you enter it in a cell as a formula, I haven't downloaded Jeff's updated file, but from his post it looks like you just need to enter

    =LargestAmount(A2)

    Then fill down as you would with any other formula.
    This is correct, and I actually populated the formula into the file as well. Look at Module1 to see the code. ALT+F11

    BTW jason.b75, I admired your formula but when I put it in the sheet I got #VALUE errors. I double checked and I entered it as an array formula.

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Formula to extract largest number from a string for Politics Dissertation

    Ah, MAC... Yes that’s good info to have on your profile.

    PowerQuery as far as I know isn’t available for MAC.

    And if I recall... VBScript.RegExp isn’t available for MAC either. If Jeff’s code uses it.
    I’ll see what I can whip up tomorrow.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Formula to extract largest number from a string for Politics Dissertation

    @harryspooner
    Since you are running a MAC, please add that information to your Office Version. It will allow users to tailor their solutions so that you don't end up wasting anyone's time, ie. CK76.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula to extract largest number from a string for Politics Dissertation

    Quote Originally Posted by CK76 View Post
    VBScript.RegExp isn’t available for MAC either. If Jeff’s code uses it.
    My code does use it, and I did not know this. I do not have access to a Mac. If I time I'll write a workaround, but only if I know the OP wants a VBA solution.

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Formula to extract largest number from a string for Politics Dissertation

    Quote Originally Posted by 6StringJazzer View Post
    BTW jason.b75, I admired your formula but when I put it in the sheet I got #VALUE errors. I double checked and I entered it as an array formula.
    The formula includes the £ sign as part of the value that is coerced by the double unary, so your regional settings probably don't recognise it. I get errors if I try is with $ amounts instead.

    Offseting the start point of the first MID function by 1 appears to resolve this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Formula to extract largest number from a string for Politics Dissertation

    See attached for UDF using alternate approach.

    Note that this UDF only handles when numbers are preceded by "(£" or "£" and when followed by null or ";".

    You could make it more dynamic by adding optional argument to the function.
    Then by combining StrConv() and Split() by vbNullChar, to check individual character.
    Attached Files Attached Files

  13. #13
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Formula to extract largest number from a string for Politics Dissertation

    @harryspooner

    Thanks for the rep

    If your query is resolved, please mark the thread as "Solved" using thread tool found at top of your initial post.

    By the way, below is the more robust function I mentioned in the post above.

    curCode is currency symbol (ex: $, £, etc).

    Function is used like... =CurLarge(A2, "$")
    If currency symbol isn't specified, it will default to "£".

    Please Login or Register  to view this content.

+ 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] Formula to extract number from a string
    By Bobbbo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-28-2017, 03:04 AM
  2. Need Formula to extract number from text string
    By tuongtu3 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-28-2015, 06:01 PM
  3. Extract the largest number
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-06-2014, 03:35 AM
  4. extracting the largest number in an alphanumeric string
    By PudseySquirrel in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-06-2014, 12:48 PM
  5. [SOLVED] Find Largest Number In String
    By mrmoc85 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2013, 12:38 AM
  6. Replies: 15
    Last Post: 01-19-2012, 07:21 AM
  7. Extract largest number in an alphanumerical string
    By chitownsox14 in forum Excel General
    Replies: 16
    Last Post: 03-15-2011, 12:16 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