+ Reply to Thread
Results 1 to 4 of 4

Mid Function from the RIGHT

  1. #1
    Registered User
    Join Date
    06-22-2015
    Location
    Hartford, CT
    MS-Off Ver
    2010
    Posts
    8

    Mid Function from the RIGHT

    Hello everyone. I am struggling a bit trying to pull some information out of a cell. I have these strings:

    ³ Sub-Cohort (SUBCOHORT)................................................................NONE ³
    ³ Embedded Value Group (EVGROUP).....................................................DEFAULT ³
    ³ Policy Count (COUNT).................................................................1.000 ³

    With each string located in a single cell. (ie A1 has the entire first line, A2 has the entire 2nd, etc.)
    My goal is to pull information from these strings and put them in a more user friendly format in excel.

    In B1:B3 I want: and in C1:C3 I want

    Sub-Cohort (SUBCOHORT) None
    Embedded Value Group (EVGROUP) Default
    Policy Count (COUNT) 1.000

    I figured out how to extract the information I needed in B1:B3.

    But I am struggling figuring out how to pull out the 1.000 for C3. I was wondering if there is a way to use the mid function from right to left. For example, I would start from the right, go left until I hit the first 0 which would be my start, then tell excel to continue to the left until it hits ".." (it can't be until "." because there is a . in 1.000). Is this possible? I hope my question makes sense.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,392

    Re: Mid Function from the RIGHT

    You can put this formula in B1:

    =SUBSTITUTE(LEFT(A1,FIND(")",A1)),"³ ","")

    and copy down to get the first part of the strings, and then this one in C1:

    =IF(ISNUMBER(--SUBSTITUTE(SUBSTITUTE(RIGHT(A1,11)," ³",""),"..","")),--SUBSTITUTE(SUBSTITUTE(RIGHT(A1,11)," ³",""),"..",""),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,11)," ³",""),"..",""),".",""))

    will give you the final parts, with numeric values converted to proper numbers (format C1 to Number with 3dp before copying down). More examples might be needed for a thorough testing.

    Hope this helps.

    Pete

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Mid Function from the RIGHT

    This will work for the specific instance of 1.000 . Of course it won't work for numbers that don't start with 1.

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


    There appears to be something strange with the example because when I copy and paste it, it acts strangely with my attempts to separate the text elements. The formula that I gave you for the specific problem does work though.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Mid Function from the RIGHT

    I used this for column B

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


    I got this to work for the entire column C

    Formula: copy to clipboard
    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. Replies: 13
    Last Post: 04-08-2014, 05:46 AM
  2. index function error using match function to get data from a cell in an array
    By mabildgaard in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2013, 02:21 AM
  3. [SOLVED] IF Function referencing IsNumber, Match, Left function on separate sheets
    By Touch9713 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2013, 10:09 PM
  4. [SOLVED] Using Offset function as the array in the PercentRank function is giving wrong result
    By Bobneil in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-06-2013, 09:29 PM
  5. Replies: 1
    Last Post: 03-21-2012, 11:22 AM

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