Free Microsoft Excel 2013 Quick Reference

Numbers to text with formatting Results

I haven't programmed in 20 years and have lost the chops I did have.

I have a small spread sheet that has a specific column range (D$$) that
needs to be searched for a text code during a SORT to determine which cells
equal the code word "done". Then for each cell with that code, the entire row
that it sits in gets grayed out to make it distinguishable from other cell
entries in column D$ range D$$ that are NOT equal to done.

After 3 sessions of trying to remember how to do this and having read the
helps sections associated with it, I continue to get errors and the formula
does not work.

My last attempt ended with a formula: =If (D$$="done",$6:$17) .

The range shown on the right end is the range of the row numbers to be
grayed out if "done" is found in a column D cell. I DO NOT want the entire
range grayed out - only those rows with cells in column D that ="done".

While I realize that the condition portion of the IF statement (left side
in parens) returns a value of 1 or 0 (true/false) depending on equality to
"done" in each cell, I don't remember if EACH cell address is passed to the
range argument on the right side to be acted upon in the conditional
formatting individually. Apparently not since it doesn't work.

A little help would be kindly appreciated.


I would like to have the cell format set as text but display numbers
like $123,456.1234 while still having the data be just 123456.1234
I know that it would be easier to set the cell format to number, but I
want to manipulate the data and the number format messes that up. I
can start out with the cell format set at text, manipulate the data,
and -then- set the cell format to number to get the proper display,
which works fine until the user enters a number in that cell a second
time and now the format is set to number.


I recently started to program in VBA for Excel.

I'm using Excel 2000 (9.0.4402 SR-1) on Windows XP.
The formatting from all the cells in the workbook and worksheet is General
(Right-click on a cell, Format cells, then tab Number, Category-listbox:

When I put a commandbutton CommandButton1 on the first worksheet of my
workbook and
I put the following code in the CommandButton1-onclick-event.

Option Explicit

Private Sub CommandButton1_Click()
Dim WeekdayNames(0 To 6) As String
Dim WeekdayColors(0 To 6) As Long

WeekdayNames(0) = "monday"
WeekdayNames(1) = "tuesday"
WeekdayNames(2) = "wednesday"
WeekdayNames(3) = "thursday"
WeekdayNames(4) = "friday"
WeekdayNames(5) = "saturday"
WeekdayNames(6) = "sunday"

WeekdayColors(0) = RGB(255, 0, 0) ' red
WeekdayColors(1) = RGB(255, 0, 0) ' red
WeekdayColors(2) = RGB(255, 0, 0) ' red
WeekdayColors(3) = RGB(255, 0, 0) ' red
WeekdayColors(4) = RGB(255, 0, 0) ' red
WeekdayColors(5) = RGB(0, 0, 255) ' blue
WeekdayColors(6) = RGB(0, 0, 255) ' blue

Dim i As Integer
For i = 0 To 6
ThisWorkbook.Worksheets(1).Range("A1").Offset(0, i).Interior.Color =
Next i

Dim TempRange As Range
Set TempRange = ThisWorkbook.Worksheets(1).Range("A1:G1")
TempRange.Value = WeekdayNames
End Sub

When I click on the button the code is executed without any errors.
The code writes the name of the day in the cells A1 till G1 and it colors
the cells with the workdays (monday till friday) as red and
the cells with the days of the weekend (saturday and sunday) as blue. No
problem until so far.

!!! But when I select cell H1 and a enter a character (for example 'm' or
'j') cell H1 is also colored red.
And when I select cell I1 and a enter a character, cell I1 is also colored
And when I select J1 and enter a value, J1 is colored red..... and so on.

When I enter a number into cell H1, nothing happens, the color of H1 does
not change.
Then I select I1 and enter a character, nothing happens, the color of I1
does not change.

It looks like there happens some kind of auto-fill for the colors of the

My questions:
1) Why does the color of H1,I1, .... changes, when I enter a character?
Maybe because the formatting of the cells is General and not Text?
Can I prevent this "auto-coloring" ?
2) How do I change the formatting of a cel by VBA-code? For example: change
from General to Text
3) TempRange.Value = WeekdayNames automatically fills a range with the
Can i make an array of RGB-values and assign this array to the
background colors of a range

Like this:
Dim WeekColors(0 To 6) As Long

Colors(0) = RGB(0,255,255)
... = .........
Colors(6) = RGB(255,128, 0)

TempRange.Interior.Color = WeekColors

kind regards,

Johan De Schutter
Software designer


Is there a way to reformat a list of numbers so the first number of each
entry does not have an apostrophe? I think it means the numbers are being
treated like text. I have tried replacing '1 with 1 and ' with (blank). I
have changed the formatting on the cell to accounting, even though I know it
will not change the text. Can this be done, because I need to reference the
numbers in a DSUM and the criteria I have are numbers not text.


I currently have a csv file with a field that has one digit numbers. I need
to create a text field and convert the one digit number to two digit text by
adding a leading zero. I have tried using the concatenate function to add
the leading zero, but when I close the csv file, it reverts back to one
digit. How can I get the file to save the field as text and include the
leading zero?

I have been reading for the better part of two hours trying all of your
suggestions. I am still unable to get the following data to sort. I have
formatted the cells. The data is similar to this:

With data going on up into the hundreds.
trying all the formulas that were out there did not bring me even close. I
am using 2007.

Thank you for your help...

I am creating a simple pivot table with one field of values numbers to be
summed and a second field, occasional two letter alpha codes which are purely
descriptive. In the source spreadsheet I have formatted the column which
contains these codes as "Text". Text is supposed to remain unchanged,
however when that column of data is entered into the pivot table as a value,
it is handled as though it were numeric data, and the two letter codes don't
show up at all. Further I see no way to manipulate this data field to be
recognized as text in the pivot table itself.
How do I get the pivot table to just take this alpha data as is?

I have a spreadsheet that is calculating intervals for equipment
maintenance. Some of the equipment must be serviced every x months,
some of the equipment must be serviced every x hours (of run time).

I have all the calculations worked out. The problem I am having is with
conditional formatting. In column A I have text that says either
"HOURS" or "MONTHS". Based on the value of that cell, I need Column B
to be formatted as either a Date (to return the date of the next
service) or as a Number (to return the hours till next service).

Any ideas on how to do that? Conditional formatting doesnt give the
option to format anything except Font, Border and Patterns. I need to
format the data type.




I saved a file which includes records in Number Formats. Then I closed it.
Next time when I opened the file All Numbers were changed to Date Format.
{God!!!!!!} Why did this happens? How can I make the changes back?
Some of the Cells must be in Text format and some in Date format and some
in Number format That is why I can't select the whole spreadsheet and make
change format to Numbers. Do I have to change each cell format manually.

My Excel is 2007 but my freinds also with 2003 had similar problem. This was
the first time I saw this.


Is there a way to use conditional formatting on a text field with leading
For example, I have a department number of 00123 and would like to highlight
all instances of this number. When entering directly in to the dialog box,
the zeros are dropped.

I am trying to get correct date from information copied from html file from
WEB excel spreadsheet.
The text is:
MANDURAH (CD) Race 1, NEW YEAR SALE (GRADE P5) Friday 11.02.2005.
The correct date is 11 February 2005.
When use command Data>text to columns and later replace “.” with ”/” I am
getting date 2/11/2005 (2 November 2005) – with value 37196. I tried format
whole spreadsheet as text or particular cells before and after operation.
What is more strange sometimes I am getting correct date in other worksheets
but I never know which date I will get.
I have also problem with pasting other data from web pages when numbers are
converted to dates against my wish. How I can stop it?
For example
A table in browser looks like this
Pool Result Div
Quinella 3-6 20.50
Exacta 6-3 33.30
Trifecta 6-3-8 154.50
Quartet 6-3-8-4 955.40

And in Excel:

Pool Result Div
Quinella 3-Jun 20.5
Exacta 6-Mar 33.3
Trifecta 6/03/2008 154.5
Quartet 6-3-8-4 955.4

With VB6 I am opening an Excel file to query the contents using ADO. I am
then using the contents of the worksheet to create a format file that is used
for a bulk insert into SQL Server. We designed the program this way so that
users can use a spreadsheet to import data in any format they want and
process the data based on values set in the spreadsheet. This keeps us from
having to design new tables and format files for new record layouts which
come down pretty often. Everything works fine usually. There is just one
perculiarity that is happening with the Excel file. Sometimes, the first
cell in the last row of the worksheet returns a blank, even though there is
definately data in the cell - a number that I am converting to a long format.
The conversion fails because the program sees an empty string ("").

I have tried retyping the data in the cell when this happens. ALSO -
Changing the cell format (to general, text, numeric) and Copying and pasting
the entire worksheet.

Usually, to get it to work, I export the entire worksheet to a text file and
then re-import the file and then my program will read that last cell.

The program is also reading all of the other cells on the last row. It is
just the first cell of the last row that it has problems with and it happens
on maybe half the files we create for the program. The number of rows in the
worksheet is different depending on how many columns are in a particular

Can anyone tell me why this is happening and what I can do to prevent it?



First, i'd like to apologize for the long winded post.
this was the best i can explain, in such short time:S (if i need to clarify
on something pls let me know)
If you can answer all, or ANY of my questions, in any way, i'd greatly
appreciate it.

I'm trying to write an excel template, that will reduce hours of work, in
notepad, into minutes.

I have a map made up of colored squares (named A1, A2, A3, B1, B2, etc etc)

What i need to do is describe that map, one square at a time. (colors being
represented by a Number)
like... NAME, #, NameNorth, #North, NameEast, #EAST, NameSOUTH, #SOUTH,
NameWest, #West

So i trashed the notepad, and opened up EXCEL and made those the column

here's an example of the first 2 rows filled in:
NOTE: I have other crap between row1 and 24. so the first square i work on,
A-1, is in row25.
NOTE ALSO: "0" means there is nothing the edge of the map)

COLUMN: NAME # North #N East #E South #S West #W
ROW25: A-1 2 A-2 3 B-1 5 0 0 0 0
ROW26: A-2 3 A-3 2 B-2 4 A-1 2 0 0
(Row27 and beyond goes all the way down to square "O-20")

this (somewhat of a) template fills in most of the blanks on its own.
The "NAME" column starts already fillled in (from "A-1" to "0-20"), and
never changes.
The "#" Column is the only one i manually input data.
EVERY OTHER COLUMNs' cell values update on their own, with a formula for
each column, copied down.

An example of C25's formula: =IF(B25=0,0,IF(B26=0,0,A26))
an example of D25's formula:
=IF(B25=0,0,IF(B26=0,0,IF(H26=4,IF(B26=5,5,IF(B26= 6,6,4)),IF(B25=6,5,B26))))
(u dont have to understand it if u dont want to)
anyway, so far, it works.

Now, here are my problems..err.. "inconveniences".

PROBLEM 1: [ auto-hide rows]

I have 324 rows (A-1 thru A-20, B-1 thru B-20, etc, down to O-20). I will
not be using every single row.
is it possible to...
Have some action bring up a pop up, asking me which rows to NOT hide
i.e. DISPLAY ROWS: A-1 through A-[5]
B-1 through B-[5]
C-1 through C-[5]
(...and so on, up to ...)
O-1 through O-[5]

(the [5] being a default, edittable value, if its possible to have one)

A filled in example:
DISPLAY ROWS: A-1 through A-7
B-1 through B-12
C-1 through C-14

icolor = 3
Case Else
icolor = 2
End Select

c.Interior.ColorIndex = icolor
Next c
End If

End Sub

Vergel Adriano recommended the additional script:

Sub ColorCells()
Dim icolor As Integer
Dim c As Range
For Each c In Range("A25:P344")
Select Case c
Case Is < 0
icolor = 3
Case 0
icolor = 51
Case 1
icolor = 45
Case 2
icolor = 4
Case 3
icolor = 10
Case 4
icolor = 5
Case 5
icolor = 48
Case 6
icolor = 9
Case Is > 6
icolor = 3
Case Else
icolor = 2
End Select

c.Interior.ColorIndex = icolor
Next c
End Sub

This script works, but i have to run it again, everytime i edit a cell value.
Is there a way to have this script, or somethign similar, continuously run?
It might make the excel sheet run slower, so perhaps have the script check
only the following cells:
D25 through D324
F25 through F324
H25 through H324
J25 through J324
L25 through L324
N25 through N324
P25 through P324

if this script can be iniated by the click of a word in a cell (like
that would be awesome.
and in the same , or similar method, have the script turn off.
I'm also looking for a script that will change cells back to their original
color (or no color at all) , altho this isn't necessary and may be too much
to ask for (if i'm not doing that already lol)

(Btw, i dont wanna use that CFPLUS add in, becuz any1 i give this file to,
they would also be required to have the add in installed)

ANY help would be appreciated with ANY of these questions.
thanks in advance

I would like to have text custom formatted so that if a 8 character
words is typed, it is automatically broken up like this XX:XXXX:XX.
This works just fine with numbers with a ##:####:## custom format,
but it will not work for text. Any suggestions?

mustard's Profile:
View this thread:

Thank you very much.. sorry , it took a while for me to reply. But, it works
with your macro.
I was wondering if you can help me with another problem that I have.
I created those pivot table with the part numbers as the primary column and
and quantity as the second column by summing the quanitty.
Then I created independent column next to it for my own purposes, but when I
refresh the pivot table, it won't refresh those independent column.
Do you know any tricks for it?

"Bernie Deitrick" wrote:

> GI,
> How about a macro: Select all the cells and run this
> Sub ConvertToText()
> Dim myCell As Range
> For Each myCell In Selection
> myCell.NumberFormat = "@"
> myCell.Value = "'" & myCell.Value
> Next myCell
> End Sub
> HTH,
> Bernie
> MS Excel MVP
> "GI" > wrote in message
> ...
> > Thanks for the help.
> > those numbers represent part numbers. There are too many mix type
> numbers.
> > After the dot, there are 4 digits and 5 digits. I tried with your
> formula,
> > it works but for pn such as 012345-001, it did not work and also between 4
> > and 5 digits.
> > Those numbers are not from external source. It was entered before and
> > provided it to me. And those numbers are formatted in cells with custom
> > number format 0#####.
> >
> > "Dave Peterson" wrote:
> >
> > > I think the next question is how do you know how many places to keep
> after the
> > > "decimal" point.
> > >
> > > 012345.2310 looks like it could have been 012345.231
> > >
> > > If you always have a dot in the part number, is it always followed by 4
> digits?
> > >
> > > =IF(MOD(A1,1)=0,TEXT(A1,"00000"),TEXT(A1,"00000.00 00"))
> > >
> > > =====
> > > If you got this list from an external source (a text file???), it might
> be
> > > easier to reimport it into excel--but specify Text for that field.
> > >
> > >
> > >
> > > GI wrote:
> > > >
> > > > Thanks for the help.
> > > > Is there a way to make the job simpler... Since I have 2000 numbers to
> > > > revise then.
> > > > I tried using =text(a1,"000000") but this one doesn't capture the part
> > > > number 012345.2310
> > > > --GI
> > > >
> > > > "Bernie Deitrick" wrote:
> > > >
> > > > > GI,
> > > > >
> > > > > Format the cells as text prior to entering the numbers, or use a
> single
> > > > > quote in front of the number string.
> > > > >
> > > > > HTH,
> > > > > Bernie
> > > > > MS Excel MVP
> > > > >
> > > > > "GI" > wrote in message
> > > > > ...
> > > > > > Hi.
> > > > > > I've been trying to convert a list of mix numbers such as 010456,
> 123456,
> > > > > > 010686-001, 010686-002, 015678-AB and much more to a pivot table.
> > > > > > Everytime
> > > > > > I created the pivot table using those numbers as pivot, EX: 010456
> > > > > > automatically changed to 10456, it doesn't recognize as 6 digits.
> I tried
> > > > > > using text, custom 0#####, still can't get it sort right.
> > > > > > Does anybody know how to solve it. Thanks a lot.
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >

(First, I'm VBA ignorant.)

I've been working on converting text strings like "SUPPX2", "PHNX3", and
"SUPP/PHNX2" into counts of services; per my examples: 2, 3, 3.

Each time I've used #s as replacements, I keep getting dates in cells that
were formatted as Text only. As described in previous posts, "1/1" becomes
"1-Jan" and "2/3" becomes "2-Mar". When I reformat the cells to Text Only,
the new dates become meaningless numbers. (I'm guessing Julian numbers for
the specified dates, but I don't read Julian and don't like having to guess
which # corresponds to the "1/1" that I wanted.) This results in my having
to undo all the work I've done, just to get back to the original.

As a result, I've been forced to locate textual replacements that wouldn't
be mistaken as numbers or Excel-recognized symbols. Example: Replacing
with "-" makes "SUPPX3" into "-X3" which Excel sees as a reference to cell
X3. (Don't know why, grrr.) Changing "PHN/SUPPX2" by the same method results
in cell errors referring to invalid formulas. (double grrr.)

I finally found that "+" either singly, or in conjunction with other "+"s or
symbols (letters), does not prompt Excel to either reformat the cell or
register an error, but this leaves me with the following process: I use the
following replacements (= is used in place of "is replaced with" & "" is a
blank): "INTAKE"=""; "PHONE"="+"; "SUPP"="+"; "+X2"="++"; "+X3"="+++";
"+X4"="++++"; "+X5"="+++++"; "+/+"="++"; "+++++++++"="9"; "++++++++"="8";
"+++++++"="7"; "++++++"="6"; "+++++"="5"; "++++"="4"; "+++"="3";
"++"="2"; "+"="1".

Is there a way to force Text Only cell formatting to be maintained despite
the presence of a number pattern or number in the cell?

(I've read other posts on this issue, but I'm wondering if anything new has
been done to rectify this problem. For Microsoft: Why not exempt the Text
Only cell format from parsing or date corrections?)

Addendum: A coworker is tracking the info, and I want to avoid increasing
that person's workload.

Mike S.

One approach that might be of interest, is something I did one time using Jim
Cone's fine Add-in called "ListFiles" and some custom VBA code. The
procedure searches a directory for image files and places them in a column in
Excel and then inserts a picture of each in the CommentBox associated with
each filename, which is also a link to the file......the picture then pops-up
as the cell is moused-over.

Vaya con Dios,
Chuck, CABGx3

"Hard Nut" wrote:

> Hello,
> I have some scientific photos stored in some picture format (in gray scale).
> I am looking for a way to import these photos into an array of text-numeber
> cells into Excel, which I can then process in Excel.
> (If no direct method exists, then optionally - the photo-convrsion to
> text-numbers can be done offline into a file, then I would have to import
> that file into Excel).
> It would be nice to have the opposite as well, namely, to export text-number
> cell arrays into som picture format.
> Can anyone advise how this can be done ?
> Thanks.

There seems to be a major bug in Excel 2007 vis-a-vis Excel 2003 with respect
to how date formats are handled. I frequently open and edit dbf files (dBase
III) in Excel and version 2007 has a problem in handling dates in such files.

Here's my setup : Regional Settings : English (US) and Short Date Format:

The dbf files have a date format of dd/MM/yyyy ( i.e. UK format)

When I used to open these files in Excel 2003, the date format shown would
be identical to that in the dbf files (dd-MM-yy). Now with Excel 2007, some
dates appear as MM-dd-yy (format changed to US) and others appear as a string
of numbers with an error comment showing that these are numbers formatted as
text. After a lot of
trial and error, it appears to me that those dates where both the day and
the month is less than or equal to 12 appear as MM-dd-yy and in all other
cases they appear as numbers formatted as text. It appears that Excel 2007 is
unable to resolve the date format if either the day or the month is greater
than 12.

For example, a date in my dbf file listed as 05/06/1984 appears as 06-05-84
while a date of 31/01/1986 appears as a text value of 19860131 and 15/08/1985
appears as a text value of 19850815.

I cannot understand why this is happening with Excel 2007 since Excel 2003
was able to import the data from the dbf files without any problems and I
have made no changes in my Regional Settings.

The only workaround I have found for this problem is to do a Text-to-Column
operation on the date columns. This is an unnecesssary extra operation which
was not required in Excel 2003.

Microsoft, please fix this problem

I did a google usenet search and found several instances similar to this
problem going back to 1999 with no real answers so there may not be, but here

I'm copying a table from a website and pasting into an Excel 2003
spreadsheet. One of the columns contains "3-0", and when I paste it, as soon
as xl sees this data it stores it as a date. I want it to stay as 3-0.
Here's what I have tried and have learned doesn't work...

* I have preset the cells, columns, and even the entire worksheet in
different attempts to text and to "custom" with "@" - no help. Data still
gets displayed as a date. when I try to change from a date format to a text
format I get the number 36586; "3-0" is lost forever.

* I have seen suggestions to do a paste special using only "values"; no
good. Since the paste is from a website the only paste special options are
"html, unicode text, & text".

* I have even seen attempts at viewing & tweaking the web site's html code,
but it's beyond me what you could do there to make the paste work differently.

Is anyone aware of any other methods to defeat Excel's insistent desire to
cheat me out of my desired cell format?



I have been trying to copy an Excel Spreadsheet into PPT, but have had
problems. In order to solve it, I created new .xls and .ppt files to create a
test, but got the same problems...

The following steps recreate the problem:

1) In a blank spreadsheet, I placed a single number in each cell, starting
at A1 and going across to AS, until there are 1 through 45 across. Format
them in some way... say Red text with an underline.

2) Set the width of all the columns to 2.00 (0.11 inches).

3) Select A1:AS

4) Copy (or Add to Scrapbook) -- The result is the same with both.

5) From Scrapbook, Paste as Picture, right there in Excel, or in PPT or in

---- You will see that everything is fine. It pasted it just as is should,
with a picture of the formated row, numbers 1 through 45

---- Now, go back and change the width of cells A1:AS to 4.29 (.17 inches),
and repeat steps 3, 4 & 5.

This time, the numbers are cut off... The pasted area is the correct width,
but only up to 27 is visible, with the rest just blank.

If you click Paste in scrapbook, it pastes all the data, but it is
unformatted. But Paste as Picture cuts it off.

From the Edit/Paste Special menu, the results are the same. -- And if you
widen the columns further, even fewer numbers are visible.

I am running a PB G4, 17" with 1GB RAM, latest OS, Updated Office 2004.

It makes no sense that the Metafile would be so small. Needless to say, this
is very annoying... Any help figuring this out would be greatly appreciated.


No luck finding an answer? You could always try Google.