+ Reply to Thread
Results 1 to 7 of 7

Extract software version up to first update from string of varying length

  1. #1
    Registered User
    Join Date
    02-13-2014
    Location
    kansas city, mo
    MS-Off Ver
    Excel 2010
    Posts
    42

    Extract software version up to first update from string of varying length

    I have 200 rows of data for a software title of different Editions and versions. In order to report on the number of installs for each edition and version, I would like to extract the version up to the first number after the first decimal. However, I have text strings that seem to have only the " - " in common. Getting the edition extracted was easy, but I am struggling to get the version into its own column. Attached is sample workbook. Any help is most appreciated. I have spent more time on this, than I thought it would take.

    Thank you,
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,734

    Re: Extract software version up to first update from string of varying length

    Missed existing formulae ..

    =SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND(" - ",A2)-2)," Mac OS","")
    Last edited by JohnTopley; 06-07-2017 at 09:48 AM.

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,520

    Re: Extract software version up to first update from string of varying length

    Can't you just combine the 2 formulas from C and D?

    =IF(ISNUMBER(FIND("Mac",RIGHT(A2,LEN(A2)-FIND("-",A2)-1))),LEFT(RIGHT(A2,LEN(A2)-FIND("-",A2)-1),FIND("Mac",RIGHT(A2,LEN(A2)-FIND("-",A2)-1))-2),RIGHT(A2,LEN(A2)-FIND("-",A2)-1))

    Or use what's already been extracted?

    =TRIM(SUBSTITUTE(SUBSTITUTE(A2,B2&"- ",""), "Mac OS",""))
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    02-13-2014
    Location
    kansas city, mo
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Extract software version up to first update from string of varying length

    I used all 3 suggestions; the first two came close to achieving what I am trying to gain, but still returning full versions. Is there a tweak that can be made that would return 13.8 instead of 13.8.1, etc,?

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Extract software version up to first update from string of varying length

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,520

    Re: Extract software version up to first update from string of varying length

    What exactly is it you want to extract?

    Could you upload a workbook with before/after data?

  7. #7
    Registered User
    Join Date
    02-13-2014
    Location
    kansas city, mo
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Extract software version up to first update from string of varying length

    AlKey, that worked! Thank you, so much!

+ 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: 9
    Last Post: 06-09-2016, 10:28 AM
  2. Need to extract specific but varying length and location numbers from description
    By 5bigun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-08-2016, 10:39 AM
  3. How to extract text from cells of varying length?
    By dukefan4141 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-12-2015, 10:42 PM
  4. [SOLVED] Extract text from end of string with varying length
    By reedersketer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2015, 05:16 PM
  5. Replies: 5
    Last Post: 09-11-2012, 08:40 AM
  6. Extracting numbers of varying length from a string
    By bethj in forum Excel General
    Replies: 3
    Last Post: 12-13-2011, 06:03 AM
  7. Replies: 6
    Last Post: 10-01-2009, 02:50 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