equivalent of Google Sheets "Split" function in Excel

**reynastus2** · 03-01-2016, 07:07 AM

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

=if(F10="","",max(ArrayFormula(vlookup(split(I10," / "), aspects, 2, 0)))+H10)

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

**jason.b75** · 03-01-2016, 09:13 AM

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.

**reynastus2** · 03-21-2016, 08:00 AM

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

**MrShorty** · 03-21-2016, 10:10 AM

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:

Function SplitUDF(strtext as string, strdelimit as string) as variant
SplitUDF=Split(strtest,strdelimit)
End function

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/

**reynastus2** · 03-21-2016, 04:14 PM

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.

equivalent of Google Sheets "Split" function in Excel

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

equivalent of Google Sheets "Split" function in Excel

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

"FILTER" equivalent function of google spreadsheet in Excel

unable view online google sheets."apologies there is no preview available"

[SOLVED] UDF to remove "equivalent" expressions from a ";" delimited string

can a "MINIF" formula be built, equivalent to "SUMIFS"?

"MAXIF" Function, or equivalent?

Mround function equivalent that does not require "add-in"

Bookmarks

Bookmarks

Posting Permissions