+ Reply to Thread
Results 1 to 7 of 7

Formula to Trim Numbers in between text and find Ageing bucket

  1. #1
    Registered User
    Join Date
    08-13-2018
    Location
    india
    MS-Off Ver
    2013
    Posts
    4

    Formula to Trim Numbers in between text and find Ageing bucket

    Hi Excel Ninjas

    I required help to find the ageing bucket (0- 5 days .6-15 days .16-30 days .30 + days ) from the below different strings ... Kindly help on formula for this

    Strings Ageing Bucket
    ---------------------------------------------------------
    Rejected - 10 Days 6-10 Days
    NoRelationship - 111 Days 30+ Days
    NoRelationship - 8 Days 6-10 Days
    Rejected - 56 Days
    Rejected - 74 Days
    NoRelationship - 0 Days
    NoRelationship - 6 Days
    Rejected - 74 Days
    Completed - 2 Days
    Rejected - 10 Days
    NoRelationship - 2 Days
    NotStarted - 3 Days
    NotStarted - 3 Days
    -

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Formula to Trim Numbers in between text and find Ageing bucket

    Thinking...
    Last edited by Special-K; 07-26-2019 at 12:11 PM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Formula to Trim Numbers in between text and find Ageing bucket

    See attached

    The formula in column E finds the first occurrence of " - " and converts it to a £ sign.
    The position of the £ is then located and one digit added to this to locate where the required number begins.
    The converted string is then searched for the first occurrence of a space to signfy where the required number ends.
    Extracting the start position of the £ +1 and (the length of where the space is minus the £ position) gives the length of the reuqired number.
    Adding 0 to this extracted string converts the number froma string into a proper number.

    Column F is a list of bucket start numbers with an additional number of 999 to keep the formula uniform in column G.

    Column G counts how many numbers are in each bucket with the bucket starting at a specific row until the value of the row below, ie 0-5, 6-15, 16-29, 30-999
    Attached Files Attached Files
    Last edited by Special-K; 07-26-2019 at 12:28 PM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,362

    Re: Formula to Trim Numbers in between text and find Ageing bucket

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    08-13-2018
    Location
    india
    MS-Off Ver
    2013
    Posts
    4

    Re: Formula to Trim Numbers in between text and find Ageing bucket

    thank you Special-K Ninja for your formula

    As per attached sheet

    I have 16 Columns with same type of data, i want to do pivot for each columns, find how many numbers in 0-5, 6- 15,16-30.30+ Days) , if i use your formula, i want 2 columns for each (1st to find numbers between string and 2nd to find aging bucket with the number)

    so 16+16 total 32 columns extra i required - i request you.. if any formula will directly give ageing bucket , so my number of columns file size will reduce for more 1000 rows

    please help Ninja !!!!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-13-2018
    Location
    india
    MS-Off Ver
    2013
    Posts
    4
    Hi Ninja

    Kindly help on ageing bucket on a single formula than 2 , to reduce my Columns and size

  7. #7
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Formula to Trim Numbers in between text and find Ageing bucket

    The solution is for the example data you provided and I don't have enough spare time to work on this.
    Sorry, I can't help further.

+ 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] pull numbers from text - trim text portion
    By jackf-nc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2017, 09:47 AM
  2. formula to find first SET of numbers in a text string
    By krunk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-02-2017, 09:06 AM
  3. Can anyone help to find ageing formula for below payment analysis
    By praveensr80 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 09-09-2016, 01:48 AM
  4. [SOLVED] Formula like MAX to find the highest value (contains text and numbers)
    By Nero_slk in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-08-2016, 03:50 AM
  5. Macro for trim forumla/ convert text to numbers
    By Peep_Show_Fan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2015, 09:37 AM
  6. Replies: 3
    Last Post: 07-04-2014, 09:57 PM
  7. trim text from numbers
    By wtcamb in forum Excel General
    Replies: 4
    Last Post: 04-19-2011, 05:26 PM

Tags for this Thread

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