## Archive for June, 2013

## How to run a background command in script as a parameter in unix shell scripts

by Frederick Tybalt on Jun.21, 2013, under Programming, Unix

Many times you would have tried running a command or script directly in a unix script by providing the name and path of the script. Also you would have run scripts are commands by assingning to variables. But by any chance if you had tried running a script or command assingned to variables you might got struck and which also gives you a vauge error message.

For example consider a script param.sh as below

echo $1 $1

And you are running the scipt as

./param.sh 'ls -ltr &'

You would be getting a error message

./& not found

This issue can be resloved by using eval command in param.sh and the new script looks as below.

eval $1

eval command will take an argument and construct a command of it, which will be executed by the shell. It is an inbuit command and there is the man page http://www.unix.com/man-page/posix/1posix/eval/

*:*Aix, eval, Shell Script, Unix more...

## How to find a substring between Nth and (N+1)th occurance of a charecter in a cell in Excel

by Frederick Tybalt on Jun.21, 2013, under Office, Windows

Excel’s built-in functions can do some pretty clever stuff, but unfortunately Excel has no single function that will return the string beween nth and n+1th charecter occurrence of specified data. Fortunately, there are ways to make Excel do this. The function used could be complex but could be accomblished.

Consider if you have the text “How to find the substring in Excel” in cell A1 and incase if you need to find 5th word in the string the below formula can be used. This can be used for strings with any seperators

=IF(ISERR(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))+1,(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))-1))),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))+1,(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))-1)))

As a Generic formula the below paramters can be changed to achive to find any word between occurances

=IF(ISERR(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))+1,(FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),m))-FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))-1))),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))+1,(FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),m))-FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))-1)))

Where

A1 – Cell of the string

C – is the Charecter occurance, can be ” “, “.” etc

n – Nth occurance of Charecter C, n starts from 0

m – N+1 occurance of Charecter C, m starts from 0

Few other useful formulas that would handle strings.

**Finding the Nth position of a charecter**

=FIND(CHAR(1),SUBSTITUTE(A1,”i”,CHAR(1),3))

This formula will inf the 3rd occurance if charecter “i” in cell A1.

Alternte formula with similar function

=SEARCHB("i",A1,(SEARCHB("i",A1,(SEARCHB("i",A1,1)+1))+1))

**Finding the first Word in a String**

=IF(ISERR(MID(A1,1,FIND(" ",A1)-1)),"",MID(A1,1,FIND(" ",A1)-1))

This formula will find the 1st word in a sting in cell A1.

*:*excel, Text between N and N+1 Char Occurance more...