Daily posts of Excel tips…and other stuff
Archive for the ‘String Functions’ Category.
I have to save a file that may have the same name as an existing file. If it does, I append a number to the end of it to make it unique. The problem is that the file will live in three different folders in its life; Working, Review, and Archive. I [...]
Recently, I had to submit a fixed width text file to a state tax agency in lieu of sending them physical copies of W-2 forms. Fixed width files contain a number of columns and each column starts at a particular position on the line. If the data in that column is shorter than [...]
A couple of weeks ago I spent some time in the bible belt attending football games. Next week I’ll be in Bush country (Austin, TX) for yet another football road trip. One of the more exciting aspects of these road trips is settling up on expenses when you get back. Depending on [...]
I have a table of numbers and formulas for each row, column, and for the table as a whole. The table is part of a report - the output of the application. The user wants to exclude certain numbers from the calculations after reviewing them. These numbers would be outliers and would [...]
Bob has a workbook with several web queries in it. In each of the web queries, the first column is a “date”. He needs to find the minimum and maximum date for all web queries.
The first problem this presents is that the “dates” aren’t really dates; they’re text. That’s why I kept [...]
There has to be a better way than this, but I can’t think of it. I have a string with a capital letter somewhere in it and I need to determine the position of that letter. The goal is to strip out all the letters before it. The test subject is the [...]
Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called “csum”):
=EVALUATE(SUBSTITUTE(A1,”,”,”+”))
Then, type =csum in B1 to obtain the result (18, in this case).
As processing strings with formulas is kind of fun just to add some more formulas for this.
1. Concatenating strings
As you probably already know Excel’s worksheet functions do not support string concatenation for an array. e.g. the following:
=CONCATENATE(A1:A10)
does not work. Typical suggestion in these cases would be to use a UDF (or an add-in such [...]
