+ Reply to Thread
Results 1 to 9 of 9

Formula using mid and length not working in the last part of formula - but not sure why

  1. 01-21-2021, 04:49 PM #1
    mightybracket123
    mightybracket123 is offline
    Forum Contributor
    Join Date
    05-09-2019
    Location
    South West
    MS-Off Ver
    365
    Posts
    108

    Formula using mid and length not working in the last part of formula - but not sure why

    Hi all,

    I'm tring to combin the MID and Length function to remove text both at the start and end of some text.

    The text in my real document always starts with the same 18 characters which I need to remove - and I can get the formula to do that. But then I also need to remove the very last character of the text and I just can't get the formula to remove that last character, but I'm not sure what I'm doing wrong.

    I've spent hours googling this and just getting no where and wondered if anyone had any ideas on where I am going wrong?

    The example I've attached shows how the text in my spreadsheet is structured, the formula I'm using, the result I get vs what I would like to achieve.

    Any advice would be much appreciated.

    Thanks in advance to anyone who has time to look at this. !
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-21-2021, 04:54 PM #2
    AliGW
    AliGW is online now
    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
    80,877

    Re: Formula using mid and length not working in the last part of formula - but not sure wh

    Try this:

    =SUBSTITUTE(MID(A2,FIND("(",A2)+1,99),")","")
    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.
    Reply With QuoteRegister To Reply
  3. 01-21-2021, 05:29 PM #3
    mightybracket123
    mightybracket123 is offline
    Forum Contributor
    Join Date
    05-09-2019
    Location
    South West
    MS-Off Ver
    365
    Posts
    108

    Re: Formula using mid and length not working in the last part of formula - but not sure wh

    HI AliGW

    thanks for this, this works

    Although I have no idea how this works. I think I can understand what parts of the formula do, but what is the function of the 1,99 ?

    Thanks so much again !
    Reply With QuoteRegister To Reply
  4. 01-21-2021, 05:31 PM #4
    AliGW
    AliGW is online now
    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
    80,877

    Re: Formula using mid and length not working in the last part of formula - but not sure wh

    The character where ( is found is one before the start of the text to extract, so add one to it. 99 because that’s a large enough figure to cover whatever is in the brackets, I hope, but could be larger if you wished.
    Reply With QuoteRegister To Reply
  5. 01-21-2021, 05:32 PM #5
    AliGW
    AliGW is online now
    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
    80,877

    Re: Formula using mid and length not working in the last part of formula - but not sure wh

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  6. 01-21-2021, 05:59 PM #6
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Formula using mid and length not working in the last part of formula - but not sure wh

    Formula for B2 =MID(A2,19,LEN(A2)-19)
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  7. 01-22-2021, 04:08 AM #7
    mightybracket123
    mightybracket123 is offline
    Forum Contributor
    Join Date
    05-09-2019
    Location
    South West
    MS-Off Ver
    365
    Posts
    108

    Re: Formula using mid and length not working in the last part of formula - but not sure wh

    Hi mehmetcik

    Thanks for this ! That also works perfectly. This formula I can more clearly understand the syntax of, although I'm not quite sure how it manages to remove the last bracket from the text in column A. Either way, this also does the job, so thank you
    Reply With QuoteRegister To Reply
  8. 01-22-2021, 04:20 AM #8
    AliGW
    AliGW is online now
    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
    80,877

    Re: Formula using mid and length not working in the last part of formula - but not sure wh

    It's not such a flexible solution, as it will only work if the text lengths are the same as your example. My solution is more flexible, as it allows any number of characters before the extracted text and up to 99 within the brackets. It gets rid of the final bracket by substituting it with a null value "".

    To fully understand formulae, get into the habit of scrutinising them using Evaluate Formula on the Formulas ribbon.

    You haven't yet marked the thread as solved.
    Last edited by AliGW; 01-22-2021 at 04:22 AM.
    Reply With QuoteRegister To Reply
  9. 01-22-2021, 07:59 PM #9
    mightybracket123
    mightybracket123 is offline
    Forum Contributor
    Join Date
    05-09-2019
    Location
    South West
    MS-Off Ver
    365
    Posts
    108

    Re: Formula using mid and length not working in the last part of formula - but not sure wh

    Thanks AliGW, that's really helpful!

    And sorry, I had a really busy day at work. I'll mark it as solved as soon as I get back onto my laptop in the morning.

    Thanks again !
    Last edited by mightybracket123; 01-22-2021 at 08:01 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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 auto-sort not working when the "IFERROR" part of the formula is in effect
    By davo3286 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-08-2019, 08:16 PM
  2. [SOLVED] Formula Help : IF(ISBLANKK) - Second part not working
    By Logit in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 01-13-2018, 09:18 PM
  3. Format a part of a text working with only value not working with formula result
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2017, 05:41 AM
  4. Cell length formula to remove any characters over 31 in length
    By tehkayd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-02-2015, 11:45 AM
  5. [SOLVED] length and alphanumeric formula (quick formula question)
    By nawGo in forum Excel General
    Replies: 4
    Last Post: 06-17-2014, 12:49 PM
  6. [SOLVED] Finding a formula that will consider length of cell and length of words
    By Mariah B in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-07-2013, 05:39 PM
  7. 2nd part of formula not working
    By dbl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2005, 03:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1