Hi,
Visit my blog and download the add-in.
https://kris*xcelpostings.blogspot.com/
Functions/Sub Routines
1. SPELLINDIAN
Syntax: SPELLINDIAN(Number)
Number can be replaced with a Cell.
e.g.
=SPELLINDIAN(1000.25) = Rupees One Thousand and Twenty Five Paise Only
or
=SPELLINDIAN(SUM(A1:A20))
2. KCONCAT
Syntax: KCONCAT(Range,Delim(Optional))
Delim – Delimiter ( by default it uses “,” (comma))
e.g
A1:A5 houses - a,b,a,c,a
B1:B5 houses – apple, one, banana, two, orange
D2 houses a
In E2,
=KCONCAT(IF(A1:A5=D2,B1:B5),"|")
It’s an array formula. So hold down CTRL + SHIFT keys while pressing ENTER key.
Now the results will be
apple|banana|orange
In E3,
=KCONCAT(B1:B5)
results - apple,one,banana,two,orange
3. CDATA
Syntax : CDATA(Cell)
It’s similar to in-built function CLEAN. But it also removes char(10),char(32),char(63),char(127) and char(160)
4. DISPLAYIMAGE
Syntax : DISPLAYIMAGE(FullPath,Wt,Ht)
FullPath – Including the extension
Wt – Width of the picture
Ht- Height of the picture
e.g.
=DISPLAYIMAGE("C:\MyPictures\Picture1.jp
or
=DISPLAYIMAGE("C:\MyPictures\” & A1 & “.jpg",200,200)
where A1 houses a picture name
5. UNIQUEVALUES
Syntax : UNIQUEVALUES(Range,Idx)
e.g.
=UNIQUEVALUES(A1:A25,2)
Outputs 2nd unique value from the range/array
Or
To get all values, in any cell and drag down
=UNIQUEVALUES($A$1:$A$25,ROWS($A$2:$A2))
or
use while drag across
=UNIQUEVALUES($A$1:$A$25,COLUMNS($B2:B2)
Sub Routines
1. ChangeCase
Select the range and run ChangeCase
You’ll be 4 options
1. Proper Case
2. UPPER CASE
3. Sentence case
4. lower case
Type the appropriate number and click OK
2. DeleteTmpFiles
Deletes all the temporary files.
3. CleanData
Same as CDATA function
Select the range and run CleanData
Kris
CAclubindia
