+ Reply to Thread
Results 1 to 5 of 5

Function to Split Bracket-Delimited Text into an Array

  1. #1
    Registered User
    Join Date
    02-10-2013
    Location
    Frankfurt, Germany
    MS-Off Ver
    Excel 2003
    Posts
    57

    Function to Split Bracket-Delimited Text into an Array

    Hi,

    I'm looking for a function that would do the following:

    SplitBracket(StringWithBrackets, Openingbracket, Closingbracket)
    SplitBracket("The [brown] fox", "[", "]")

    Returns an array:

    "The "
    "[brown]"
    " fox"

    It should also be able to handle brackets that are longer than one character. Does anyone know of something that might work?

    Many thanks!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Function to Split Bracket-Delimited Text into an Array

    To be fair, that looks like Split with a space delimiter.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    02-10-2013
    Location
    Frankfurt, Germany
    MS-Off Ver
    Excel 2003
    Posts
    57

    Re: Function to Split Bracket-Delimited Text into an Array

    Hi,

    It's a little different, because I'm not interested in the spaces. In another example, the output would be:

    SplitBracket(StringWithBrackets, Openingbracket, Closingbracket)
    SplitBracket("The [brown] fox jumps over the [lazy] dog.", "[", "]")

    Returns an array:

    "The "
    "[brown]"
    " fox jumps over the "
    "[lazy]"
    "dog."

    I think I need a user defined function, but I was hoping someone had already written it.

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

    Re: Function to Split Bracket-Delimited Text into an Array

    I was hoping someone had already written it.
    The behavior you describe sounds a lot like the behavior of Google Sheets built in Split function https://support.google.com/docs/answer/3094136?hl=en If I put that text into a Google Sheets cell and enter =SPLIT(A1,"[]") into an adjacent range, it outputs an array just like you describe. VBA's Split() function behaves differently, and Excel does not even have a built in Split function, so this really only works in Google Sheets. If you are not absolutely required to do this in Excel/VBA, and Google sheets is a viable option, then this might be the easiest approach.

    I won't speak for others, but I think if I were trying to do this in VBA, I would change the input string into something that would "fit" into the behavior of VBA's Split function. https://docs.microsoft.com/en-us/off...split-function

    If I had control over the input string, rather than using open [ and close ] brackets, I would use one or the other.
    Please Login or Register  to view this content.
    If I had to allow the input to have both characters, I would use a Replace function (https://docs.microsoft.com/en-us/off...place-function ) to change the input string to something more suitable for the Split() function:
    Please Login or Register  to view this content.
    Putting something like this into a UDF should be straightforward.

    Some variation on one of those approaches is probably how I'd do it.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Function to Split Bracket-Delimited Text into an Array

    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. Importing a delimited text file with different data types into an array
    By ifthenifthen in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-20-2018, 06:43 PM
  2. Split function won't split all delimited values
    By quetzalc0atl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-17-2017, 11:49 AM
  3. [SOLVED] How to Convert a delimited text to row.. using excel function..?
    By Vikas_Gautam in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-15-2014, 08:55 AM
  4. Split text into array using multiple delimiters
    By TKFRMjarvis in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-26-2014, 03:07 PM
  5. using the split function on an array of strings
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-31-2013, 12:52 AM
  6. Importing Delimited Text/Exporting Delimited Text Loop
    By cecarter74 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-05-2009, 01:17 PM
  7. [SOLVED] Best way to import fixed-width delimited text files into an array?
    By KR in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2005, 11:06 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