My Menu ver 1.0 - User Defined functions

User Defined functions

The following udf's will only work if you have installed this addin.

1 hyper_extract - it will extract the hyperlink address from the cell. For example cell a1 is having some text with hyperlink

In cell b1 = hyper_extract(a1) . it will return the hyperlink address. Snapshot-

2 know_formula It will return the formula applied on the cell. Snapshot below-

You want to know the formula written in cell a4 type know_formula(A4) in b4

3 run_formula If you have formula written in a cell and you want to know the result. Snapshot-

For eg - You have formula written in cell a3 and you want to know the result type =run_formula(A3) in b3


4 cell_isbold - check if cell value is bold or not. For eg.- If you want to know if cell is bold or not.Type cell_isbold(A1). It will return True if cell is bold. Snapshot

5 extract_nums - It will extract numeric characters from a cell .Type like extract_nums(A1)

Note- it will ignore "dot"(.) sign . Snapshot

6 extract_letters - it will extract letter from cell .Type like - extract_letters(A1)

7 extract_last_word -It will extract last word in a cell .Type like extract_last_word(A1)

8 extractnum_frmleft - It will extract all numeric charters from left till non numeric .Type like extractnum_frmleft(a1)

9 extractnum_frmright -It will extract all numeric charters from right till non numeric .Type like extractnum_frmright(a1)

10 extract_comment - it will return the comment added to the cell. Type like extract_comment(A1)

11 cellcount_fill to count the cells value with some background color - Type like cellcount_fill(H9,F8:F14)

12 cellcount_font to count cell values with some font color type like cellcount_font(H9,F8:F14)

13 exwithdot_num - it will extract numeric value from a cell and will include dot (only one dot in the cell first from left) . Type Like exwithdot_num(A1). snapshot below-

14 con_nonblanks - It will concatenate the non blank cells with the separator passed con_nonblanks(A1:F1,";")

15 reverse_text - will reverse the text written on cell

16 reverse_words

If you want to reverse the words separated by space or special character . For example "ashish koul " as "koul ashish" . etc Snapshot below

Use this udf

Function reverse_words(x As String, spl As String)
Dim z, s As String
z = Split(x, spl)
For j = UBound(z) To LBound(z) Step -1
s = s & spl & z(j)
Next
reverse_words = Right(s, Len(s) - 1)
End Function

Type lik reverse_words(A3," ")

a3- cell having word
" " - space as separator


17 pick_word


if you cell value is separated by a space or comma etc. and you want to pic 2nd word or 3 rd etc form a cell without applying text to column or find ,etc. Snapshot below

Here is the udf


Function pick_word(x As String, spl As String, positon As Integer)
Dim z
z = split(x, spl)
pick_word = z(positon - 1)
End Function

Type like pick_word(A3," ",2)



a3 - is cell having value

" " - space is used as separator

2 - which word to be picked