+ Reply to Thread
Results 1 to 4 of 4

UDF that performs like google sheets SPLIT function

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

    UDF that performs like google sheets SPLIT function

    Hi All

    I asked about this in the excel formula forum and they have pointed me here.

    Basically I have the following formula working well in google sheets
    Please Login or Register  to view this content.
    breaking it down - the if statement is there only to make the cell blank and pretty before there is anything populated in the cell, the cell I9 is usually one word but sometimes it is 2 and those words are split by a / and spaces on either side (so looks like Charm / Tenacity or similar).

    IF there are 2 words I would like the UDF to vlookup both words and figure out the value associated with those words and use the highest value when compared to each other and then add these with a value in H9 that is populated via other means.

    Here is a link to the workbook with the working cells as done in google docs
    https://docs.google.com/spreadsheets...it?usp=sharing

    And I have attached the file in question as well.

    Thanks in advance

    Rey
    Attached Files Attached Files

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

    Re: UDF that performs like google sheets SPLIT function

    Did you try the one I suggested in the other thread?
    Please Login or Register  to view this content.
    I know it was very simple, but it should behave very similarly to Google's SPLIT() function.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

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

    Re: UDF that performs like google sheets SPLIT function

    Sorry I misread your post in the other forum. Just woke up and before coffee and read it as that it might work and I should look more into it not that it was probably going to work ...

    Ok so I've put that into a module in the workbook but yes I am very new to UDF's as this is the first. That tutorial you linked, like so many others is a great explanation but also clear as mud when talking something specific which I am asking for help with... I have used VBA in the past but definitely not proficient with it and don't really know where to start (hence the wanting to avoid it in the first place).

    You said that once the module is inserted the the function would be entered =SplitUDF(arg1,arg2) as a horizontal array function (ctrl-shift-enter) across as many cells as needed.

    Can you please explain this a little more? as in how to implement this in my worksheet. Is this going to then form part of my formula on the sheet itself (ie. =max(vlookup(SplitUDF( )) and if so what would that formula look more like?

    Sorry I am not a programmer nor have I ever claimed to be and most of the formulaic content of that workbook I have entered is very basic indeed.

    Thanks once again for your help.

    Rey

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

    Re: UDF that performs like google sheets SPLIT function

    You said that once the module is inserted the the function would be entered =SplitUDF(arg1,arg2) as a horizontal array function (ctrl-shift-enter) across as many cells as needed.
    Can you please explain this a little more?
    This UDF will return an array, so it will be used much like the LINEST() and FREQUENCY() and other array functions that return multiple values. If I have "abc/def" in A1 and want to have the output in B1:C1, I select B1:C1, enter the formula =SplitUDF(A1,"/") and confirm with ctrl-shift-enter. B1 contains "abc" and C1 contains "def".

    From there, it will be about combining and nesting functions. I know that UDF's sometimes behave differently from built in functions when nested into MAX() type array functions, so there may need to be some further adaptation.

+ 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. equivalent of Google Sheets "Split" function in Excel
    By reynastus2 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-21-2016, 04:14 PM
  2. email row contents based on cell values (google sheets populated by google forms)
    By reedg in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-13-2016, 02:55 PM
  3. Google Sheets Query IF column problem Converted to Excel Function?
    By Garthilk in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-24-2015, 10:37 PM
  4. Import function in Google Sheets
    By Bielbo in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 10-27-2014, 06:15 AM
  5. need 'continue' function. how to update to new google sheets?
    By eusair in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 10-15-2014, 11:36 PM
  6. Google Sheets - Using IF and SPLIT together?
    By dracogram in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 07-16-2014, 09:57 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