[go: up one dir, main page]
Scribd
Upload
126 views66 pages

ASAP Utilities Excel Functions Guide

The document describes various worksheet functions added by the ASAP Utilities add-in. It lists 31 functions including functions to get cell color, count characters, extract parts of file paths, check font properties, and more. It provides examples of how each function is used in a formula with brief explanations. Additional information on ASAP Utilities can be found on their website.

Uploaded by

ANas Ali
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLS, PDF, TXT or read online on Scribd
126 views66 pages

ASAP Utilities Excel Functions Guide

The document describes various worksheet functions added by the ASAP Utilities add-in. It lists 31 functions including functions to get cell color, count characters, extract parts of file paths, check font properties, and more. It provides examples of how each function is used in a formula with brief explanations. Additional information on ASAP Utilities can be found on their website.

Uploaded by

ANas Ali
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLS, PDF, TXT or read online on Scribd
You are on page 1/ 66

Worksheet functions added by ASAP Utilities

To use these worksheet functions you need to have ASAP Utilities installed.
Click on the function to view an example of its use:
1

=ASAPCELLCOLORINDEX()

18

=ASAPGETFORMULA()

=ASAPCOUNTBYCELLCOLOR()

19

=ASAPGETFORMULAINT()

=ASAPCOUNTBYFONTCOLOR()

20

=ASAPGETHYPERLINK()

=ASAPCOUNTCHAR()

21

=ASAPGETNUMBERFORMAT()

=ASAPCOUNTSHADES()

22

=ASAPISBOLD()

=ASAPEXTRACTFILENAME()

23

=ASAPISFORMULA()

=ASAPEXTRACTFOLDERNAME()

24

=ASAPLOADIMAGE()

=ASAPEXTRACTNUMBERS()

25

=ASAPMERGECELLS()

=ASAPFILENAME()

26

=ASAPRANDOMPASSWORD()

10

=ASAPFILEPATH()

27

=ASAPSHEETNAME()

11

=ASAPFILEPROPERTIES

28

=ASAPSPELLNUMBER()

12

=ASAPFONTCOLORINDEX()

29

=ASAPSTRIPNUMBERS()

13

=ASAPFULLFILENAME()

30

=ASAPSUMBYCELLCOLOR()

14

=ASAPGETCOMMENT()

31

=ASAPSUMBYFONTCOLOR()

15

=ASAPGETDOMAIN()

16

=ASAPGETFONTNAME()

17

=ASAPGETFONTSIZE()

Note

Remarks on calculation and distribution

More information is on our website:


http://www.asap-utilities.com/asap-utilities-worksheet-functions.php
The macro in this workbook automatically updates the references from the ASAP Utilities formulas
to the program folder if needed. This can for example be the case if ASAP Utilities is not installed
in the default C:\Program Files\ASAP Utilities\ folder but for example
C:\Program Files (x86)\ASAP Utilities\
If the macro does not run automatically then you can press the button below:

http://www.asap-utilities.com

back

#ADDIN?

###
Color

Cell color
index number:
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column B:


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

###

You can use this function to count the number of cells that have a certain color.
The first parameter is the range to be searched, the second parameter is either the cell that has the color (rec

Example 1
Use the =ASAPCOUNTBYCELLCOLOR() formula to count the number of yellow cells:
We use B10 in the formula to have the formula count the cells with the same cell color as cell B10
Result:
Formula used in B10:
###
Err:511
34
18
92
102
63
55

81
73
27
30
40
13

75
64
91
15
61
72

11
56
58
19
32
4

103
41
36
97
8
69

88
101
54
71
51
79

12
84
45
17
44
3

105
28
24
70
67
42

20
77
66
33
35
99

83
21
68
10
38
9

Example 2
Use the =ASAPCOUNTBYCELLCOLOR() formula to count the number of green cells:
The colorindex number for the green color is 4
Result:
Formula used in B23:
###
Err:511
12
52
1
31
72
51

2
3
52
148
22
28

123
53
261
128
262
75

81
72
9
3
136
220

21
90
84
2
26
84

18
111
1
244
44
44

103
161
35
13
0
119

66
101
18
112
22
36

3
6
74
51
2
157

257
141
0
196
17
114

er the cell that has the color (recommended), or Excel's colornumber.

cell color as cell B10

back

#ADDIN?

###

You can use this function to count the number of cells that have a certain font color.
The first parameter is the range to be searched, the second parameter is either the cell that has the fontcolor

Example 1
Count the number of cells with a blue font:
We use B10 as a reference in the formula to have the formula count the cells with the same cell color as cell
Result:
Formula used in B10:
###
Err:511
34
18
92
102
63
55

81
73
27
30
40
13

75
64
91
15
61
72

11
56
58
19
32
4

103
41
36
97
8
69

88
101
54
71
51
79

12
84
45
17
44
3

105
28
24
70
67
42

20
77
66
33
35
99

83
21
68
10
38
9

103
161
35
13
0
119

66
101
18
112
22
36

3
6
74
51
2
157

257
141
0
196
17
114

Example 2
Count the number of cells with a red font:
The colorindex number for the red color = 3
Result:
Formula used in B23:
###
Err:511
12
52
1
31
72
51

2
3
52
148
22
28

123
53
261
128
262
75

81
72
9
3
136
220

21
90
84
2
26
84

18
111
1
244
44
44

er the cell that has the fontcolor (recommended), or Excel's colornumber.

with the same cell color as cell B10

back

#ADDIN?

#ADDIN?
Text
Test
Bastien Mensink
blah blah blah
Microsoft Excel 2010
15
Text
keyword1, keyword2, keyword3
a, b, c, d
a|b|c|d|e
114
http://www.test.com/t/test
The formula is Case sensitive
Cc

How many spaces?


Err:511
Err:511
Err:511
Err:511
Err:511
Character to count
,
,
|
1
/
s
c

This way the formula is not Case sensitive:


Cc
c

Formula used in column C:


Err:511
Err:511
Err:511
Err:511
Err:511
How many times?
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Err:511

You can also use it to count words by counting all the spaces and add 1:
Use it to count words
How many words?
Formula used in column C:
how many words are this?
Err:511
Err:511
and this?
Err:511
Err:511
now
Err:511
Err:511
Same technique, but with a workaround if there are double spaces between the cells
Use it to count words
How many words?
Formula used in column C:
how many words are this?
Err:511
Err:511
and this?
Err:511
Err:511
now
Err:511
Err:511

How to do this without the use of ASAP Utilities:


0 Err:511
1 Err:511
2 Err:511
2 Err:511
0 Err:511
Formula used in column C:
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

2
3
4
2
4
4
1

Err:511

2 Err:511

Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

5 Err:511
2 Err:511
1 Err:511

een the cells


5 Err:511
2 Err:511
1 Err:511

e use of ASAP Utilities:

back

#ADDIN?

###
You can use this function to count the number of colored cells (so called "shades").

Example
Use the =ASAPCOUNTSHADES() formula to count the number colored cells.
Result:
Formula used in B6:
###
Err:511
34
18
92
102
63
52
1
31
72
51

81
73
27
30
40
3
52
148
22
28

75
64
91
15
61
53
261
128
262
75

11
56
58
19
32
72
9
3
136
220

103
41
36
97
8
90
84
2
26
84

88
101
54
71
51
111
1
244
44
44

12
84
45
17
44
161
35
13
0
119

105
28
24
70
67
101
18
112
22
36

20
77
66
33
35
6
74
51
2
157

83
21
68
10
38
141
0
196
17
114

back

#ADDIN?

#ADDIN?
Returns the file name from a full path and filename.

Example
Full path and filename
D:\Projects\Archive\Client 1\Balance.xls
\\FS\Projects\Archive\Client 1\Balance.xls
D:\Projects\Client 14\Sales.xlsx
D:\Projects\Client 14\Prices to be updates.xlsm

Result:
Err:511
Err:511
Err:511
Err:511

Formula used in column C


Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

#ADDIN?
Returns the folder name from a combined filepath and filename.

Example
Full path and filename
D:\Projects\Archive\Client 1\Balance.xls
\\FS\Projects\Archive\Client 1\Balance.xls
D:\Projects\Client 14\Sales.xlsx
D:\Projects\Client 14\Prices to be updates.xlsm

Result:
Err:511
Err:511
Err:511
Err:511

Formula used in column C


Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

#ADDIN?
Returns the numbers from a text string.
The result is a number. Therefore leading zeros are not preserved.

Example
Value
8011 LB
ART.47654
PROD0004431
D232SE
BE-54
978-90-274-3964-2.
NL.8173.03.954
Year published: 1984
ISO9001

Result:
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

are not preserved.

Formula used in column C


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

#ADDIN?
Returns the name of your workbook. This is the name of the workbook without the filepath (folder).

Example
The name of this example workbook:
Result:
#ADDIN?
Formula used above
Err:511

filepath (folder).

back

#ADDIN?

#ADDIN?
Returns the filepath (the folder) where your workbook is stored.

Example
The folder of this example workbook:
Result:
#ADDIN?
Formula used above
Err:511

back

#ADDIN?

#ADDIN?
Returns the value of one of the built-in document properties for the current workbook.
You can refer to document properties either by index value or by their English name.

Example
1
2
3
4
5
7
9
10
11
12
18
20
21
29

Property
Title
Subject
Author
Keywords
Comments
Last Author
Application Name
Last Print Date
Creation Date
Last Save Time
Category
Manager
Company
Hyperlink Base

lue of one of the built-in document properties for the current workbook.
o document properties either by index value or by their English name.

Result:
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column C


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

#ADDIN?
Text colored
Blue
Red
Dark grey
Green
Orange
Lighter grey
Amsterdam
Athens
Berlin
Bern
Brussels
Budapest
Copenhagen
Dublin
Helsinki
London
Luxembourg
Madrid
Moscow
Oslo
Paris
Prague
Reykjavk
Rome
Stockholm
Vienna

Font color index number:


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column C:


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

#ADDIN?

Returns the full filename of your workbook. This is the name of the workbook including the folder (filepath) where it is save

Example
The folder of this example workbook:
Result:
#ADDIN?
Formula used above
Err:511

older (filepath) where it is saved.

back

#ADDIN?

#ADDIN?
Returns the comment-text from a cell.

Example
Cells with comment
Test
500
19%
35

Comment contents:
Err:511
Err:511
Err:511
Err:511

Formula used in column D


Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

#ADDIN?

Returns the (sub)domain from a given hyperlink (website address/url).


This can be useful if you are for example analyzing website stats to quickly see which refers come from the s

Example without protocol


Value
Result
http://www.asap-utilities.com
Err:511
http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=259&utilities=97#ASAPGETDOMAIN
Err:511
http://blogs.msdn.com/b/excel/
Err:511
http://twitter.com/#!/msexcel
Err:511
http://twitter.com/#!/ASAPUtilities
Err:511
http://twitter.com/#!/ASAPUtilities
Err:511
ftp://ftp.microsoft.com
Err:511

Example with protocol


Value
Result
http://www.asap-utilities.com
Err:511
http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=259&utilities=97#ASAPGETDOMAIN
Err:511
http://blogs.msdn.com/b/excel/
Err:511
http://twitter.com/#!/msexcel
Err:511
http://twitter.com/#!/ASAPUtilities
Err:511
ftp://ftp.microsoft.com
Err:511

ee which refers come from the same domain.

Formula used in column D


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column D


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

#ADDIN?
Returns the name of the font in a cell.

Example
Value
Test value
Test value
Test value
Test value
Test value
Test value
Test velue

Result
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column D


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

#ADDIN?
Returns the name of the font in a cell.
You can also use this to sort by font-size.

Example
Value

Test value

Result
Err:511
Err:511
Err:511
Err:511

Formula used in column D


Err:511
Err:511
Err:511
Err:511

Test value

Err:511

Err:511

Test value

Err:511

Err:511

Test value

Test value

Test value

back

#ADDIN?

###
Returns the formula of a cell.
This formula is used allover this workbook to show you which formulas are used

Example
Formula
144
557
701
350.5
5/Aug/16
click
pretty large
blah blah blah
125

Result
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column D


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

###

Returns the formula of a cell in the "international" (English) notation.


The English names for the formulas will be used, the list separator is a comma and the decimal separator is a
The largest resources on the Internet on Excel are in English. On these websites the "international" formulas

Example
Formula
144
36
180
90
5/Aug/16
click
not so large
blah blah blah
125

Result
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

omma and the decimal separator is a point.


websites the "international" formulas and style are used. If you use a local version of Excel you can now easily create an "international"

Formula used in column D


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

w easily create an "international" example of the formula you used.

back

#ADDIN?

#ADDIN?

Returns the (sub)domain from a given hyperlink (website address/url).


This can be useful if you are for example analyzing website stats to quickly see which refers come from the s

Example
Value with hyperlink
click here
or click here
official excel blog
MS Excel on Twitter
ASAP Utilities on Twitter

Get the used hyperlink:


Err:511
Err:511
Err:511
Err:511
Err:511

Value with hyperlink


in this workbook:
TOP
Back to the index

Get the used hyperlink:


Err:511
Err:511

ee which refers come from the same domain.

Formula used in column D


Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column D


Err:511
Err:511

back

#ADDIN?

#ADDIN?
Returns the number format of a cell.

Example
Value
1,250
Test
Text
8/5/2016
8/5/2016 17:30
5,000.00
$5,000.00
Err:511
0012
41%
250
(250)

Result
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column D


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

#ADDIN?
Returns TRUE if the cell is bold or FALSE if it isn't.

Example
Value
sort
sort code
php sort
sort array
bubble sort
java sort
sql sort
sort by
mysql sort
javascript sort
perl sort
sort of
php sort array
sort by color
bank sort code
sort arraylist
insertion sort
selection sort
list sort
sort algorithm
datagrid sort
mysql sort by
sql sort by
merge sort
c++ sort
sort datatable
quick sort
vba sort
sort on

Total bold cells:

Result
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column D


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Err:511

back

#ADDIN?

#ADDIN?
Returns TRUE if the cell has a formula or FALSE if it doesn't.

Example
Value or formula
79%
17%
83%
85%
10%
38%
28%
50%
8%
31%
96%
5%
66%
65%
78%
97%
62%
67%
40%
77%
34%
56%
91%
32%
54%
71%
40%
87%
85%

Total formula cells:

Result
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column D


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Err:511

back

#ADDIN?

#ADDIN?
Inserts the specified image as an object and puts it at the left-top of your cell.

To update the image, you can replace the formula with a new image name.
To remove the image you have to remove both the formula and the image. (The image isn't removed if only th
You have to press Control+Alt+F9 to have the formula recalculated.

Example
Image path and file name or url
http://www.asap-utilities.com/press/box-shot-asap-utilities-left-angle-big.png

http://www.asap-utilities.com/press/logo-asap-utilities-small-200px.png

C:\Windows\Web\Wallpaper\Windows\img0.jpg

C:\WINDOWS\WEB\WALLPAPER\WINDOWS\IMG0.JPG

C:\Windows\Soap Bubbles.bmp

r cell.

me.
ge. (The image isn't removed if only the formula is removed.)

Result

Formula used in column D

Err:511

Err:511

Err:511

Err:511

Err:511

Err:511

Err:511

Err:511

Err:511

Err:511

back

#ADDIN?

#ADDIN?

Joins several text strings into one text string.


An easy alterative for the Excel =CONCATENATE() function. The benefit of this ASAP Utilities function:
- You can specify a range to join, for example "A1:G1".
- The number format of the values will be used.
For example if a cell has the value "12.23072" and the number format is to display only one decimal then th
- You only have to specify a delimiter once.
- By default empty cells will be ignored.

Example 1
Values
1/80s
5.7f
ISO: 400
12.0mm
November 12, 2010

Result
Err:511
Formula used above
Err:511

Example 2
Values
apple
pear
banana
orange

Result
Err:511
Formula used above
Err:511

Example 3
Payment is due:
November 11, 2010
November 18, 2010
November 25, 2010
December 2, 2010

Result
Err:511
Err:511
Err:511
Err:511

Formula used
Err:511
Err:511
Err:511
Err:511

Example 4
Values
BEAV
BESI
AATK
BFAM
CCOMP
BNHN
ACLS
BIOM
CCRD
BBBY
BFEN
BBGI
CBUK
BIORY

Result
Err:511
Formula used above
Err:511

It would require a long formula without ASAP Utilities


BEAV, BESI, AATK, BFAM, CCOMP, BNHN, ACLS, BIOM, CCRD, BBBY, BF
Formula used above
Err:511

BASI
BCIS
AALA
BFRE
BEIQ
BKNW
BRKC
BSXT
ACTT
ABIZ
CALD
BRKS
AFAM
ACME
CAFEW
ABAX

t of this ASAP Utilities function:

is to display only one decimal then this function uses value as "12.2".

Formula used
Err:511
Err:511
Err:511
Err:511

ut ASAP Utilities
NHN, ACLS, BIOM, CCRD, BBBY, BFEN, BBGI, CBUK, BIORY, BASI, BCIS, AALA, BFRE, BEIQ, BKNW, BRKC, BSXT, ACTT, ABIZ, CA

RKC, BSXT, ACTT, ABIZ, CALD, BRKS, AFAM, ACME, CAFEW, ABAX

back

#ADDIN?

#ADDIN?
Returns a random string that can be used as a password.
The minimum lenght is 8, the maximum length is 32767

Example 1
Password
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Length
#ADDIN? #ADDIN?
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Example 2, without special characters


Password
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Length
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

be used as a password.
imum length is 32767

Formula used in column C


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

special characters
Formula used in column C
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

#ADDIN?

Returns the name of the worksheet this formula is used on.


We use this also to dynamically create the hyperlinks to the formulas on our website in the link on each works
For example in the link in cell B1 on the worksheet ASAPRANDOMPASSWORD we use the following formula
Err:511
which results in the following hyperlink address:
http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=259&utilities=97#ASAPRANDOMPASSWO

Example
The name of this example workbook:
Result:
#ADDIN?
Formula used above
Err:511

e in the link on each worksheet.


e use the following formula:

ASAPRANDOMPASSWORD

back

#ADDIN?

#ADDIN?
Returns a spelled-out number or amount in the languages English, Dutch or German

Example 1, no currency
Value
125.00
89.99
125.00
89.99
125.00
89.99
125.00
89.99
125.00
89.99
125.00
89.99

Result
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Example 2, with currency


Value
125.00
89.99
125.00
89.99
125.00
89.99
125.00
89.99
125.00
125.00
89.99
125.00
89.99
125.00
89.99
125.00
89.99
125.00

Result
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column D


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

Formula used in column D


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

#ADDIN?
Removes all numbers from a text string and removes all spaces at the beginning and end of the result.

Example
Value
8011 LB
ART.47654
PROD0004431
ZZE5
ISO9001
Capital Sheet Metal Inc 32020
Capitol City 16361
Carreker Corporation 015509

Result:
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

all spaces at the beginning and end of the result.

Formula used in column C


Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511
Err:511

back

#ADDIN?

###
Calculates the SUM of the cells that have a certain fill color.

Example 1
Use the ASAPSUMBYCELLCOLOR() formula to sum the values of the yellow cells:
We use B10 in the formula to have the formula count the cells with the same cell color as cell B10
Result:
Formula used in B9:
###
Err:511
34
18
92
102
63
55

81
73
27
30
40
13

75
64
91
15
61
72

11
56
58
19
32
4

103
41
36
97
8
69

88
101
54
71
51
79

12
84
45
17
44
3

105
28
24
70
67
42

20
77
66
33
35
99

83
21
68
10
38
9

Example 2
Use the =ASAPSUMBYCELLCOLOR() formula to sum the values of the green cells:
The colorindex number for the green color is 4
Result:
Formula used in B22:
###
Err:511
12
52
1
31
72
51

2
3
52
148
22
28

123
53
261
128
262
75

81
72
9
3
136
220

21
90
84
2
26
84

18
111
1
244
44
44

103
161
35
13
0
119

66
101
18
112
22
36

3
6
74
51
2
157

257
141
0
196
17
114

cell color as cell B10


Look at the formula you would normally have to use in Excel:
497

Err:511

And now image that the colors have changed and you need to update the formula with the method used abo

ula with the method used above

back

#ADDIN?

###

You can use this function to count the number of cells that have a certain font color.
The first parameter is the range to be searched, the second parameter is either the cell that has the fontcolor

Example 1
Use the =ASAPSUMBYFONTCOLOR() formula to SUM the values of cells with a blue font:
We use B10 in the formula to have the formula count the cells with the same cell color as cell B10
Result:
Formula used in B10
###
Err:511
34
18
92
102
63
55

81
73
27
30
40
13

75
64
91
15
61
72

11
56
58
19
32
4

103
41
36
97
8
69

88
101
54
71
51
79

12
84
45
17
44
3

105
28
24
70
67
42

20
77
66
33
35
99

83
21
68
10
38
9

Example 2
Use the =ASAPSUMBYFONTCOLOR() formula to SUM the values of cells with a red font:
The colorindex number for the red color = 3
Result:
Formula used in B23:
###
Err:511
12
52
1
31
72
51

2
3
52
148
22
28

123
53
261
128
262
75

81
72
9
3
136
220

21
90
84
2
26
84

18
111
1
244
44
44

103
161
35
13
0
119

66
101
18
112
22
36

3
6
74
51
2
157

257
141
0
196
17
114

er the cell that has the fontcolor (recommended), or Excel's colornumber.

th a blue font:
cell color as cell B10

th a red font:

Look at the formula you would normally have to use in Excel:


820

Err:511

And now image that the colors have changed and you need to update the formula with the method used above

ula with the method used above

back

You might also like