+ Reply to Thread
Results 1 to 5 of 5

equivalent of Google Sheets "Split" function in Excel

  1. #1
    Registered User
    Join Date
    11-04-2014
    Location
    NSW Australia
    MS-Off Ver
    2013
    Posts
    29

    equivalent of Google Sheets "Split" function in Excel

    Hi All

    Just wondering if there is an equivalent to the Google Sheets SPLIT function for excel (Running excel 2013)

    Below is the formula that I am attempting to replicate

    Please Login or Register  to view this content.
    so breaking it down

    the if part of the formula is just there to hide cell contents.

    The max and arrayformula parts seem to be ok but the split in the vlookup is not working the way it should. Basically the cell that the vlookup references has either a single word or on occasion two words split with a /. The worksheet then looks that up in the named range aspects for the highest number relating to said aspect and then adds the number for that aspect to H10 in the same sheet.

    I'm trying to do this without having to VBA so if there is a function that could work that anyone can think of that would be awesome.

    Thanks in advance

    Rey2

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

    Re: equivalent of Google Sheets "Split" function in Excel

    Assuming never more than 2 words, it would be easiest with something like

    =IF(F10="","",MAX(VLOOKUP(IFERROR(LEFT(I10,FIND("/",I10)-1),I10),aspects,2,0),VLOOKUP(IFERROR(MID(I10,FIND("/",I10)+1,100),I10),aspects,2,0))+H10)

    If you need it to work with more than 2 words then you might have to resort to a UDF.

    It appears to be a simple task, but I think trying to do it with native excel functions will be anything but.

  3. #3
    Registered User
    Join Date
    11-04-2014
    Location
    NSW Australia
    MS-Off Ver
    2013
    Posts
    29

    Re: equivalent of Google Sheets "Split" function in Excel

    Sorry to necro a thread here but I still can't get my head around what that formula is doing. Yes it is only ever two words with a " / " inbetween.

    I have attached the sheet and highlighted the cell in question and hidden all the other pages.

    Thanks in advance

    Rey
    Last edited by reynastus2; 03-21-2016 at 04:12 PM.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: equivalent of Google Sheets "Split" function in Excel

    I would probably use a different approach. Google's SPLIT() function appears to function very similar to VBA's Split() function. https://msdn.microsoft.com/en-us/lib.../gg278528.aspx It's not exact, but it's pretty close. I would probably build a simple VBA UDF (User Defined Function) that uses VBA's Split function to provide this functionality to Excel. The UDF might be as simple as:
    Please Login or Register  to view this content.
    the function would be entered =SplitUDF(arg1,arg2) as a horizontal array function (ctrl-shift-enter) across as many cells as needed.

    If you are new to UDF's, here's a quick introduction: http://computeraidedfinance.com/arti...xcel-with-vba/
    Last edited by MrShorty; 03-21-2016 at 10:12 AM.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    11-04-2014
    Location
    NSW Australia
    MS-Off Ver
    2013
    Posts
    29

    Re: equivalent of Google Sheets "Split" function in Excel

    Thanks for the help.

    I was hoping to not have to use VBA and have managed to make the workbook function appropriately so far but I guess there is nothing for it and I'll move this thread to the appropriate help forum.

    Thanks once again

    Rey.

+ 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. "FILTER" equivalent function of google spreadsheet in Excel
    By excelismagic in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-03-2015, 08:22 AM
  2. unable view online google sheets."apologies there is no preview available"
    By choy96 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 09-09-2014, 02:24 AM
  3. [SOLVED] UDF to remove "equivalent" expressions from a ";" delimited string
    By dredwolf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-08-2012, 07:25 PM
  4. can a "MINIF" formula be built, equivalent to "SUMIFS"?
    By jrtaylor in forum Excel General
    Replies: 4
    Last Post: 10-22-2011, 12:44 PM
  5. "MAXIF" Function, or equivalent?
    By Oriana in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-01-2008, 07:43 AM
  6. [SOLVED] Mround function equivalent that does not require "add-in"
    By Roundy in forum Excel General
    Replies: 4
    Last Post: 04-17-2006, 11:10 AM

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