68 | Chapter2, Hacking Excel’s Built-in Features
#26
Use Replace... to Remove Unwanted Characters
HACK
H A C K
#26
Use Replace... to Remove Unwanted Characters
Hack #26
When importing data or copying and pasting data from other sources into
Excel, unwanted characters appear throughout your spreadsheet. Using this
hack, you can spare yourself the trouble of removing them by hand.
Excel’s Replace... feature can help you remove unwanted characters from
your spreadsheet, but it takes a fewextra steps. For instance, you can
replace cells containing unwanted characters with nothing (effectively delet-
ing them) so that they no longer exist. To do this, you need to knowthe
character code of the characters you want removed. All characters have a
character code, and Excel will tell you what it is if you apply the
CODE
func-
tion to them. The
CODE
function returns a numeric code for the first charac-
ter in a text string. The returned code corresponds to the character set used
by your computer.
To make this work, select one of the cells containing an unwanted charac-
ter. From the Formula bar, highlight the character and copy it to the clip-
board. Then select any unused cell (A1, for example) and paste the character
into the cell on its own.
In another cell, enter the following formula:
=CODE($A$1)
This returns the character code of the unwanted character.
Select all your data, select Home
Editing
Find & Select
Replace
(pre-2007, Edit
Replace…), click the Find What: field, press the Alt or
c
key, and enter
0
followed by the code number the
CODE
function returned. If
the code number is 163, press the Alt or
c
key and type
0163
.Leave the
Replace With: field empty and click Replace All. This will very quickly
remove all the unwanted characters matching that character code. Repeat
these steps for each unwanted character.
H A C K
#27
Convert Text Numbers to Real Numbers
Hack #27
The contents of a cell might look like numbers, especially in imported data,
but it still might be impossible to use these numbers in calculations. Here are
afew ways in which you easily can convert these “text” numbers to true
numbers.
Remember that numbers in Excel are right-aligned by default, and that text
is left-aligned by default. One easy way to identify those problematic text
numbers in a column of what you think is composed entirely of true num-
bers is to select the column, go to the Home tab, launch the Alignment
options by clicking the dialog launcher in the bottom right corner of the
Pdf save rotated pages - rotate PDF page permanently in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Empower Users to Change the Rotation Angle of PDF File Page Using C#
rotate individual pdf pages reader; pdf rotate pages separately
Pdf save rotated pages - VB.NET PDF Page Rotate Library: rotate PDF page permanently in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
PDF Document Page Rotation in Visual Basic .NET Class Application
how to rotate all pages in pdf at once; how to rotate one pdf page
Convert Text Numbers to Real Numbers #27
Chapter2, Hacking Excel’s Built-in Features | 69
HACK
Alignment group, select Format Cells
Alignment (pre-2007, Format
Cells
Alignment), ensure that the horizontal alignment is set to Excel’s
default of General, and click OK. Widen the column to a reasonable width,
and all true numbers will be aligned to the right while any problematic text
numbers will be aligned to the left. Dates will also be aligned to the right, as
a date’s true underlying value is nothing more than a number.
Using Paste Special
Nowthat you knowyou have numbers that are being seen as text, here is a
quick and easy way to convert them all to true numbers, making Excel con-
sider them usable for calculations. Copy any blank cell and then select your
list of numbers. Right-click, select Paste Special... and then select Values
under the Paste options. Select Add under the Operation options and click
OK.
This will change to true numbers any numbers that are being seen as text.
This happens because a blank cell has a value of
0
,and when you add any
number to a number that Excel is treating as text, you will force the text
number to become a true number.
Using the TEXT Functions
You can apply this logic to some of Excel’s standard functions—in particu-
lar, Excel’s
TEXT
functions. Usually, when you use any of Excel’s
TEXT
func-
tions and the result returned is a number, Excel will still return that number
as a text value rather than as a numeric value.
Assume you have a range of cells starting from $A$1. Each cell contains a
dollar amount, followed by a space, then a person’s name. Using the follow-
ing formula, which combines the two
TEXT
functions
LEFT
and
FIND
,you can
extract this dollar value:
=LEFT(A1,FIND(" ",A1)-1)
If cell A1 contains the data $22.70 Fred, the formula’s result will be $22.70.
However, this result will be returned as text rather than as a true numeric
value; therefore, by default it will be left-aligned within the cell.
You can modify the formula so that the result is no longer a text value, but
rather, a true numeric value, by adding
0
to the value:
=LEFT(A1,FIND(" ",A1)-1)+0
This will force the dollar value returned to become a true number; there-
fore, it will be right-aligned by default. All you need to do now is format the
cell accordingly.
VB.NET TIFF: Rotate TIFF Page by Using RaterEdge .NET TIFF
specific formats are: JPEG, PNG, GIF, BMP, PDF, Word (Docx Save the rotated page(s) to new a TIFF Multiple image formats support for saving rotated TIFF page(
how to save a pdf after rotating pages; pdf rotate page and save
VB.NET Image: Image Rotator SDK; .NET Document Image Rotation
VB.NET image rotator control SDK allows developers to save rotated image as are dedicated to provide powerful & profession imaging controls, PDF document, tiff
how to permanently rotate pdf pages; how to rotate all pages in pdf
70 | Chapter2, Hacking Excel’s Built-in Features
#28
Extract the Numeric Portion of a Cell Entry
HACK
H A C K
#28
Extract the Numeric Portion of a Cell Entry
Hack #28
Often, you might have entries in your lists that contain both text and numbers
within them. Using a custom function, you can easily take care of this
problem, regardless of the length of the string.
Another problem that can arise regarding text and numbers occurs when
you mix text and numbers in the same cell, with no real way of extracting
the numeric portion only. In this case, you can use a custom function to
extract the numeric portion from a text string.
To create this custom function, press Alt/Option-F11, select Insert
Mod-
ule, and enter the following code:
Function ExtractNumber(rCell As Range)
Dim lCount As Long, l As Long
Dim sText As String
Dim lNum As String
sText = rCell
For lCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, lCount, 1)) Then
l = l + 1
lNum = Mid(sText, lCount, 1) & lNum
End If
If l = 1 Then lNum = CInt(Mid(lNum, 1, 1))
Next lCount
ExtractNumber = CLng(lNum)
End Function
Press Alt/
c
-Q to exit and save. The function will appear under User Defined
in the
Paste
function (Shift-F3). Use the function as shown inFigure2-10.
InFigure2-10, column A contains a mixture of text and numbers, column B
contains the result of using the
ExtractNumber
function, and column C
shows how the formula looks in column B.
Figure 2-10. Extracting the numeric portion from a text string
C# TIFF: How to Rotate TIFF Using C# Code in .NET Imaging
VB.NET How-to, VB.NET PDF, VB.NET Word, VB Tiff page, like sorting and saving the rotated Tiff page 0); page.Rotate(RotateOder.Clockwise90); doc.Save(@"C:\rotate
rotate pdf pages individually; rotate pages in pdf expert
How to C#: Rotate Image according to Specified angle
VB.NET How-to, VB.NET PDF, VB.NET Word, VB.NET Excel, VB.NET PowerPoint, VB.NET Tiff, VB.NET Imaging, VB.NET OCR, VB.NET Twain, VB Save the rotated image to
rotate single page in pdf; rotate pdf page
Customize Cell Comments #29
Chapter2, Hacking Excel’s Built-in Features | 71
HACK
H A C K
#29
Customize Cell Comments
Hack #29
Cell comments enable you to place the electronic equivalent of a sticky note
to any specified cell in a worksheet. Although many people use cell
comments, many don’t know that cell comments are customizable.
When you insert a cell comment via Review
Comments
NewComment
button (pre-2007, Insert
Comment), by default Excel will also insert the
username for the PC being used. You can change this by selecting the Office
button
Excel Options
Popular (pre-2007, Tools
Options
General;
on the Mac, Excel
Preferences
General). The username will appear at
the bottom of the dialog box that opens, where you can type whatever you
want to be shown by default.
Although cell comments serve the simple purpose of displaying a message to
either yourself or another user, you can customize the cell comment so that
it better reflects your intentions.
To make this hack work, you will need to add the Change Shape button to
the Quick Access toolbar. Do this by going to the Office button
Excel
Options
Customize. Then, from the Choose Commands From drop-
down, select the Smart Art Tools/Format tab. Locate the Change Shape tool,
click on it, and then select Add to add to the Quick Access toolbar, fol-
lowed by OK (pre-2007, you won’t be able to add this tool, so ensure that
the Drawing toolbar is displayed by selecting View
Toolbars
Drawing).
Insert a cell comment into a cell by selecting the cell, going to the Review
tab, and selecting Comments
NewComment (pre-2007, Insert
Com-
ment). This automatically places you in Edit mode, ready to enter text into
the comment box.
Left-click the outside border of the cell comment so that you are no longer
in Edit mode. With the comment selected, select the Change AutoShape
tool from your Quick Access Toolbar (pre-2007, select Drawfrom the
Drawing toolbar, then Change AutoShape). You will be presented with a list
of options including Basic Shapes, Block Arrow, Flow Chart, Stars and Ban-
ners, and Callouts. Choose an option, and the cell comment will change to
the shape selected, as shown inFigure2-11.
If you are using Excel 2007, you will notice that any shape you choose will
have a three-dimensional look. If you want to create this look in pre-2007
versions, ensure that your comment is still selected, but that you are no
longer in Edit mode. On the Drawing toolbar, click the Shadow Settings
icon shown in Figure2-12 and choose a shadowsetting for the cell
comment.
VB.NET Imaging - Data Matrix Plug-in SDK Control
Generated Data Matrix barcode image can be freely rotated, resized and code page.AddImage(image, New PointF(100F, 100F)) docx.Save("C:\\Sample_Barcode.pdf").
change orientation of pdf page; how to rotate a pdf page in reader
C# HTML5 Viewer: Load, View, Convert, Annotate and Edit OpenOffice
documents, CSV file and Text file are allowed to be rotated. PowerPoint (.ppt, .pptx) on webpage, Convert CSV to PDF file online Users can save annotations to
permanently rotate pdf pages; how to rotate a single page in a pdf document
72 | Chapter2, Hacking Excel’s Built-in Features
#29
Customize Cell Comments
HACK
Adding a Picture
Another interesting thing you can do with cell comments is use them to dis-
play pictures without impinging on any associated data. For instance, you
could insert a picture of a chart into a cell comment to better illustrate the
data in the chart without having to show the chart all the time.
To add a picture, ensure that the cell comment is selected, but that you are
not in Edit mode. Left-click and select Format
Comment, (pre-2007 users
can double-click the Comment border). Select Colors and Lines from the
Format Comment dialog box. From the Color options, select Fill Effects,
and from the Fill Effects dialog, select Picture. Nowbrowse to the picture
you want to insert into the cell comment.
Figure 2-11. A dramatically formatted cell comment
Figure 2-12. Shadow Settings options
VB.NET Word: VB.NET Code to Rotate Word Page Within .NET Imaging
Here, we can recommend you VB.NET PDF page rotating tutorial and multi any original quality during or after the Word page rotating; Save the rotated Word page
rotate pdf page and save; how to change page orientation in pdf document
VB.NET Image: How to Process & Edit Image Using VB.NET Image
permanently? A 2: This VB.NET image editor control SDK allows developers process target image file and save edited image as new file.
rotate pdf page permanently; how to rotate all pages in pdf in preview
Sort by More Than Three Columns #30
Chapter2, Hacking Excel’s Built-in Features | 73
HACK
Extracting Comment Text
One last thing you can do to cell comments is extract the text that was
placed into a cell comment and have it appear in a cell. To do this, you need
to place a simple custom function into a standard module. Select Developer
Code
Visual Basic (pre-2007, Tools
Macro
Visual Basic Editor) or
select Alt/Option-F11, then select Insert
Module and enter the following
code:
Function GetCommentText(rCommentCell As Range)
Dim strGotIt As String
On Error Resume Next
strGotIt = WorksheetFunction.Clean _
(rCommentCell.Comment.Text)
GetCommentText = strGotIt
On Error GoTo 0
End Function
To return to Excel, either click the Close button or press Alt/
c
-Q, then save
your workbook. Now, in any cell, enter the following formula:
=GetCommentText(A1)
where A1 has a cell comment in it. The comment text will appear in the cell.
H A C K
#30
Sort by More Than Three Columns
Hack #30
Excel’s Sort feature is limited in that it enables you to nominate no more than
three data fields by which to sort. In most cases, this is enough, but
sometimes it can be handy to sort by more than three columns of data. Here
is how you can get around this limitation.
For this example, we will assume you have related data in columns A, B, C,
D, and E, and you want to sort this data first by column A, then B, then C,
then D, and then E. To do this, you need to be able to sort backward—in
other words, sort by the last field first, and then work back to the first field.
Select columns A through E and then select Data
Sort. Select the sort
order by specifying that column C be sorted first, then D, and then E. Click
Sort. Nowselect columns A through E and select Data
Sort. This time,
sort by column A and then by B. Click Sort, and everything will be in order.
Excel will have sorted the columns by five fields instead of the usual three.
If you want to automate this task, you can use a macro that will sort the
selection and guess whether your data has column headings based on the
formatting of the first rowin the selection. If headings are in boldface, Excel
will know they are column headings and will not sort them. Instead, it will
sort by the leftmost column first, through to the rightmost column, for any
number of columns up to 256.
74 | Chapter2, Hacking Excel’s Built-in Features
#31
Random Sorting
HACK
The macro code you need to use must be placed into a standard module. To
get it there, select Tools
Macro
Visual Basic Editor (Alt/Option-F11),
then select Insert
Module and enter this code:
Sub SortByX()
Dim l As Long
For l = Selection.Columns.Count To 1 Step -1
Selection.Sort Key1:=Selection.Cells(2, l), _
Order1:=xlAscending, Header:=xlGuess, Orientation:=xlTopToBottom
Next l
End Sub
To return to Excel, either close the window or press Alt/
c
-Q. Once you
have the hang of it, you will be able to perform much more complicated
sorts than just the standard types on offer.
H A C K
#31
Random Sorting
Hack #31
You can use Excel to pick three winners—1st, 2nd, and 3rd—chosen at
random from a list in your spreadsheet. The easiest and fairest way to do this
is to use Excel’s RAND function in combination with its sorting capabilities.
Assume you have a three-column table in your spreadsheet, starting from
column B and containing Name, Age, and ID No., in that order. You can
place the
RAND
function in cell A2 and copy this down as many rows as
needed, all the way to the end of your table. As soon as you do this, each cell
in column A containing the
RAND
function will automatically return a ran-
dom number by which you can sort the table. In other words, you can sort
columns A, B, C, and D by column A in ascending or descending order, and
the three winners can be the top three names.
The
RAND
function is a volatile function that will recalculate automatically
whenever an action takes place in Excel—e.g., entering data somewhere
else, or forcing a recalculation of the worksheet by pressing F9. You’d better
write down your winners quickly.
However, you can use this volatility to your benefit and record a macro that
sorts data immediately after you recalculate, and force the
RAND
function to
return another set of random numbers. You then can attach this macro to a
button so that each time you want to draw three winners, all you need to do
is click the button and use the top three names.
For example, assume you have your data in columns B, C, and D and that
row1 is used for headings. First, place the heading
RAND
in cell A1. Enter
=RAND()
in cell A2 and copy down as far as needed. Then select any single
cell and select Developer
Code
Record Macro (pre-2007, Tools
Macro
Record New Macro…).
Manipulate Data with the Advanced Filter #32
Chapter2, Hacking Excel’s Built-in Features | 75
HACK
Select columns A, B, C, and D and press F9 (to force a recalculation; on the
Mac, use
c
-=). Select Sort & Filter options
Data
Sort and sort the data
by column A. Stop recording the macro.
Next, select Controls Options
Developer
Insert (pre-2007, View
Toolbars
Forms). Select a button from the Forms toolbar and place it any-
where on the worksheet. Assign the macro you just recorded to this button
and click OK. (Change the text for the button from Button 1 to something
more meaningful, if you want.)
You can select column A and hide it completely, as there is no need for a
user to see the random numbers generated. Each time you click the button,
your data will be sorted randomly, and you can just read off the top three
names to be the winners, as shown inFigure2-13.
The
RAND
function in Excel 2003 and Excel 2007 has a major
flaw. Although the Help file clearly states the random num-
ber returned will be between 0 and 1, this is not always the
case if the
RAND
function is used in many cells. Sometimes the
RAND
function will return a number less than 0. To read
Microsoft’s take on why they changed the algorithm, visit
http://support.microsoft.com/default.aspx?kbid=828795.
H A C K
#32
Manipulate Data with the Advanced Filter
Hack #32
If you are familiar with Excel’s AutoFilter tool, you also are familiar with its
limitations. If you require extensive data manipulation, using Excel’s
Advanced Filter tool is the way to go.
Although limited, AutoFilters are a useful way to display only the data that
meets particular criteria. Sometimes, however, you cannot glean the infor-
mation you need using the standard options available in AutoFilters. Excel’s
versatile Advanced Filter tool enables you to further manipulate your data.
Figure 2-13. The end result of a random sort with column A hidden
76 | Chapter2, Hacking Excel’s Built-in Features
#32
Manipulate Data with the Advanced Filter
HACK
When you use Excel’s Advanced Filter tool, your table must be set up in a
classic table format as described at the start ofChapter1.
When using Excel’s Advanced Filter tool, you will need a copy of your
table’s column headings somewhere above your data. You should always
leave at least three blank rows above your table of data. To ensure that your
headings are exactly the same and will remain so regardless of whether you
change your column headings, always reference the column headings with a
simple reference formula such as
=A4
,where
A4
contains a column heading.
Copy this across for as many column headings as you have in your table.
This will ensure that the criteria headings for the Advanced Filter are
dynamic. Directly belowthese copied headings, place the criteria for the
Advanced Filter to use. For more details on this process, see the Excel Help
under Advanced Filters Criteria.
When using the Advanced Filter, keep in mind that two or more criteria
placed directly underneath the applicable heading use an
OR
statement. If
you want to use an
AND
statement, the column headings and their criteria
must appear twice, side by side.Figure2-14 shows how to use the
OR
opera-
tor to filter your data, andFigure2-15 shows how to use the
AND
operator.
Both of the preceding examples showfairly simple uses of the Advanced Fil-
ter tool and can be accomplished via AutoFilter if needed. Next we’ll pro-
vide some examples of the Advanced Filter in which the use of AutoFilter
would not be possible.
Figure 2-14. Using Advanced Filter with OR to show only those people who have a pay
rate greater than $16.00 OR less than $15.00
Manipulate Data with the Advanced Filter #32
Chapter2, Hacking Excel’s Built-in Features | 77
HACK
It’s important to note that whenever you use a formula for
your criteria, you must not use above the criteria a heading
that is identical to the one within the table. For example, if
you have a list of numeric data in column A and the list
begins in cell A5 (with A4 being the heading), and you need
to extract all the numbers in that listthat are greater than the
average, you would use criteria such as these:
=A5>AVERAGE($A$5:$A$500)
If the criteria were placed in cell A2, the criteria range would
be $A$1:$A$2, but $A$1 could not contain the same head-
ing as the one the list uses. It must be either blank or a differ-
ent heading altogether.
It also is important to note that any formula you use should
return either
TRUE
or
FALSE
.The range for the Average func-
tion is made absolute by the addition of dollar signs, while
the reference to cell A5 is a relative reference. This is needed
because when you apply the Advanced Filter, Excel will see
that A5 is a relative reference and will move down the list
one entry at a time and return either
TRUE
or
FALSE
. If it
returns
TRUE
,it knows it needs to be extracted. If it returns
FALSE
,it does not meet the criteria; therefore, it will not be
shown.
Assume that many of the names are repeated in the range $A$5:$A$500,
with A4 being the headings. Also assume that many of the headings are
repeated numerous times. You have been given the task of extracting from
Figure 2-15. Using Advanced Filter with AND to show only those people who have a
name starting with B AND ending with L
Documents you may be interested
Documents you may be interested