Jan Karel inspired me with his use of Excel4 Macros to Refer to the previous sheet and to determine page numbers to use this “old” Excel feature for something else: Automatic calculations with the current selected range.

To start first define the name Sel_Range with the formula:
=MID(CELL(“address”,($A$1,SELECTION())),6,255)&TEXT(NOW(),””)

Sel_Range returns a string representing the current selected range. Using INDIRECT this string can be converted to a range reference and used in the following type of formulas on your sheet:
=SUM(INDIRECT(Sel_Range))
=AVERAGE(INDIRECT(Sel_Range))
=MIN(INDIRECT(Sel_Range))
=MAX(INDIRECT(Sel_Range))

Notes:

• After entering this formula you first get a circular reference error. Just hit F9 after leaving the cell to refresh the formula result
• To re-calculate the formula(s) simply press F9
• And as always for using Excel4 macros: In versions prior to Excel 2003 DON’T copy formulas containing names using these macros as this will crash Excel

Happy new year
Frank Kabel

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 as MOREFUNC.XLL with the function MCONCAT).
For only small ranges you could still do it with worksheet functions. The problem is how to deal with blank cells if you want to use a delimiter between each filled cell and without checking each single cell for blank.

So let’s have a look at the following example:

Formula used (in cell F1): =SUBSTITUTE(TRIM(A1&” “&B1&” “&C1&” “&D1),” “,”-“)

As you can see this formula concatenates (using the hyphen as delimter) the strings in column A-E and skips blank cells. This formula has one restriction though: The concatenated cells are not allowed to contain spaces. So if you need a more complex solution best to use VBA for this.

2. Splitting strings in characters
The following is probably more a fun application but if you want to split a string value into its single characters you can achieve this with the following formula:

Formula used (in C1): = MID($A1,COLUMN()-COLUMN($C1),1)
and copy this formula across.

Frank

Deutsche Version / German version

Now continuing the first part of string manipulations with formulas the following will describe how to extract the n^th element of a larger string. This technique is for example useful in the following cases:

• Extract first, middle and last name
• Get the last or first word of a sentence (words separated by spaces)

As an assumption for the following I’d assume that words in a larger string are separated by spaces. e.g. “This is a longer text of words separated by spaces”
So now let’s start extracting the individual elements of this string

1. Getting the first element
Getting the first word is rather simple. Just combine the functions LEFT and FIND (and add some error checking):

=IF(LEN(A1)=0,””,IF(ISNUMBER(FIND(” “,A1)),LEFT(A1,FIND(” “,A1)-1),A1))

This formula searches for the first occurence of a space character and returns all characters to the right of this space. If no space character is found the complete string is returned. For our example this formula would return “This”.

2. Getting the last element
Now it’s getting a little bit more complicated as we don’t know in advance how many elements our string contains and Excel does not provide a FIND function which searches from the right (as you have in VBA with the method InStrRev). But is is still possible to achieve with formulas alone. We start with getting the position of the last delimiter (the last space character):

1. Using SUBSTITUTE and FIND:
   =FIND(“^^”,SUBSTITUTE(” “&A1,” “,”^^”,LEN(” “&A1)-LEN(SUBSTITUTE(” “&A1,” “,””))))-1
2. Using LOOKUP:
   =LOOKUP(2,1/(MID(” “&A1,seq,1)=” “),seq)-1
   where seq is again a defined name with the formula: =ROW(INDIRECT(“1:1024”))

Personally I prefer the second alternative as it is shorter (and for me easier to remember). But both formulas should return the same result. For our example theywill both return: 44
Now the only thing you have to do use this formula part within a MID formula (and don’t forget to add 1 to the position to skip the space):

1. Using SUBSTITUTE and FIND:
   =MID(A1,FIND(“^^”,SUBSTITUTE(” “&A1,” “,”^^”,LEN(” “&A1)-LEN(SUBSTITUTE(” “&A1,” “,””))))-1+1,1024)
2. Using LOOKUP:
   =MID(A1,LOOKUP(2,1/(MID(” “&A1,seq,1)=” “),seq)-1+1,1024)

Both formulas would return the string: “spaces”

Getting the n^th element
Adapting the approach of SUBSTITUTE and FIND for getting the last element you could also get the position for the n^th and n+1 element within a string. Using this you get a kind of ‘monster’ formula:

=IF(OR(LEN(A1)=0,ISERROR(FIND(“^^”,SUBSTITUTE(” “&A1,” “,”^^”,$B$1)))),””, MID(A1,FIND(“^^”,SUBSTITUTE(” “&A1,” “,”^^”,$B$1)),IF(ISERROR(FIND(“^^”,SUBSTITUTE(
A1,” “,”^^”,$B$1))),1024,FIND(“^^”,SUBSTITUTE(A1,” “,”^^”,$B$1)))-FIND(“^^”,SUBSTITUTE(
” “&A1,” “,”^^”,$B$1))))

where B1 stores the desired element number n.

And as stated in the first part of this article this could of course be achieved much easier using VBA but maybe it wouldn’t be that much fun …

Frank

Pages: 1 2