In spreadsheet work, there are many cases when you may want to save a complex set of data in a machine readable format. One strategy is to save the data in a comma-delineated string. That means a set of text where each individual data point is separated by a comma.
At its core, comma-delineated string is a simple database table row.
We use strings like these to save the tax plans we discover for a specific client as a part of our Tax Planning service. We can save what Roth conversion targets we are using in that plan for each year across all 100 years of the client’s lifetime. Then, when it comes time to run the plan in the new year, we can simply load and parse our old strings.
Create the Comma-Delineated String
You can create a comma-delineated string easily by using the TEXTJOIN function . When you are saving data, that formula might look like:
=TEXTJOIN(“,”,FALSE,A1:A4)
The comma is our delimiter. The FALSE means that empty variables will be included. A1:A4 is an example of a range which could be joined.
Locate the Nth Variable
The formula to return (the number stored in $B$1)th variable in your comma-delineated string (stored in A5) is:
=MID(A5,FIND(CHAR(134),SUBSTITUTE(A5,”,”,CHAR(134),$B$1))+1,FIND(CHAR(134),SUBSTITUTE(A5,”,”,CHAR(134),$B$1+1))-1-FIND(CHAR(134),SUBSTITUTE(A5,”,”,CHAR(134),$B$1)))
You’ll see that there are three separate instances of nestled SUBSTITUTE FIND functions. The SUBSTITUTE function replaces the Nth instance of the old text (a comma) with the very unique character of a sword † . Then, the FIND function locates the sword character.
We do this three times. Twice to find the comma before the selected variable and once to find the comma after the variable. Then, we use the MID function to return the text after the before-comma sword and for the length of the difference between the after-comma and the before-comma swords. Meanwhile, the plus one and minus one in those two bits removes the commas themselves.
Hopefully, our articles on interesting Excel formulas empower your own Excel efforts. If you enjoy running investment analysis in Microsoft Excel, you may want to consider applying for a job at Marotta Wealth Management!
We are an independent, fee-only, comprehensive wealth management firm offering a complete range of investment management and financial planning services. The work we do for clients is valuable, fun, and fulfilling. As fee-only fiduciaries, our only concern is to help clients meet their goals. And as comprehensive wealth managers, we are always seeking to advance the financial planning profession.
You can read more about the benefits of working for us here.
Photo by Mimi Thian on Unsplash. Image has been cropped.
