Replace null string with blank cell
I have a large table which was created via lookups from other tables.
Many of the entries are (or should be) blank. After the table was
created, I converted everything to values (Paste Special/Values) in
order to save memory. However, the table now contains a null string or
other unprintable character in each of the "blank" cells. Thus, if I
use END-DN or END-UP to find the next value in the table, the cursor
goes to the end of the table, as it sees something in each cell. If I
edit a "Blank" cell, I see no characters, and if I select the formula
bar & hit ENTER, ...show first few letters of a column in another cell
I know how to use left to show the first character of a particular column in
What I need to do is show the first seven characters.
Column A has a value of "McDonald"
I want Column B to show "McDonal"
=LEFT(A1,7) if A1 is your reference cell.
"Glenn" <email@example.com> wrote in message
>I know how to use left to show the first character of a particular column
> another column.
> What I need to do is show the first seven characters.
>...micro for multi colors in a cell
i am very new to this and need some help with setting up a micro
I have a spreadsheet with a cell that i will have 4 options
"A" "B" "C" "D"
and i would like for each of them to have a different color when pu
into that cell.
Please i am new to this so you will have to walk me through the step
Subject: Re: seguridad xp
From:...Can I add to a cell value without manual calculation?
I have a value in a cell eg 100 and want to add another value to it eg 10.
Can this be done by entering the additional value and the sum completed
You can do it like this. Put 10 in a cell and copy it.
Select the cell with 100 in it and right click - paste special - select
'ADD' - OK
Delete the 10 from the cell
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
> I have a value in a cell eg ...how to find data and list all of them in a cell.
I have one database table like this.
Col_A Col_B Col_C
name room cancel
person1 building1 no
person2 building1 yes
person3 building2 no
person4 building2 no
person5 building1 no
what I need is to list the name who don't cancel in building1 and fill in
the result would like to be : person1 , person5
pls. help me to solve this problem.
thank you so much
The simplest way would be to select the ...Linking Dialog box content with cells in "regular" sheet
How to link data in Edit box (from Dialog caption) with exact cells in
"regulal" Sheets? Or is there another way to link exact content from Dialog
sheet to normal sheet? For example, if I have number 200 in Dialog, I need
that exact number 200 on another ("regular") sheet to preform calculations
with it. thnx
...Need help with putting text in a shape
I am making an a4 flyer, i have used a background photo over which i have
placed a shape which i plan to type in. The problem is i need to rotate the
shape 90 degrees for what i want it to look like. Having rotated the shape i
type in it and the writing goes top to bottom (it too has been rotated) and i
want it to go left to right.
Imagine a piece of A4 paper, you write along the top and then twist the
paper right 90 degrees, thats how my text looks, and i want it left to right
with the shape staying where i put it.
Any ideas how i can change the text direction, i have look...Optimize event selection
Hi, I am trying to select the best event for a particular day. There are 3 types of possible events, and there can be multiple instances of the same possible event. I need to figure out which is the longest contiguous event for each event type, and then which is the longest contiguous event out of all the event types. Here is a sample from my spreadsheet where 1st col is time, next 3 cols are the event types, called ones, twos & threes. What I would like is a table showing the longest contiguous span of time for each event type, and which is the longest of all the spans for the day.
Time...Help : Cell Flags
In Excel 2003, some of the cells in my spreadsheet have a small triangle
indicator in the top left corner. What does this indicator signify, and how
can I remove it from my spreadsheet? Thanks for any help.
You can turn them all off -- or even use the warning to correct something.
> In Excel 2003, some of the cells in my spreadsheet have a small triangle
> indicator in the top left corner. What does this indicator signify, and how
> can I remove it from my spreadsheet? Thanks for any help.
Thanks Dave....how to color code a cell in excel if amount reaches a minus
what is the formula that is used if any to color code a cell or many cells
that the amount reaches minus within a spreadsheet, such as total is -$5.69
in a roww and you may have others that may go in the minus
How can you color code that cell and others if the amount reaches a minus?
I would really appriciate any help because I am working on a expenditure
The feature to do this is called Conditional Formatting
Highlight the cells you wish to format, then do Format >
ConditionalFormatting > CellValueIs > LessThan > 0 > Format > PatternsTab...Printing plain text selection
Alright, I've dug through the groups and realize that this is a
limitation of Outlook but I can't for the life of me understand why
Outlook is more limited than Outlook Express when it comes to printing
a selection or select pages for plain text messages.
Can anyone offer any sound reasoning or tell me if MS intends to change
...Selecting Data from Another Record
I have a table that contains an event number and the event date.
tblEvent: fldNo, fldDate
When someone is invited to the event, in case they can't make it, I want a
query to show the fldDate from the next record.
qryEvent: fldNo, fldDate, fldNextDate
How do I get the query to look at the next record to get the fldNextDate?
One approach is:
NextDate: DMin("[fldDate]","tblEvent","[fldDate]>#" & [fldDate] & "#")
Steve Schapel, Microsoft Access MVP
Rob B. wrote:
> I have a table that contains an event nu...Flipping order of contents of a cell based on a coma
I have the following information in a column:
VIGNEAULT, NATALIE A
RYAN, BRANDON D
HALLEY, ANDREA S
SCOTT, BRIAN L
GOMES, MARIA L
NABIGON, JOHN E
COLE, CHARLOTTE A
I need to flip the contents around so that VIGNEAULT, NATALIE A would
become NATALIE A VIGNEAULT (without the coma).
Any ideas would be greatly appreciated!
Copy down as needed.
<firstname.lastname@example.org> wrote in message
>I have the following in...Format Cells #14
I'm trying to figure out a way (if there's one) to enter a number into a
blank cell and have it automatically multiply that cell by 25.4 (essentially
converting a number from in. to mm.), keeping the new number in that same
cell: enter 2 into A2 and it converts it to 50.8 and A2 now shows 50.8.
Is this possible?
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If .Column = 1 Then
.Value = .Value * 2.54
Application.EnableEvents ...Error when using selection/text to columns
Having a little difficulty here. Using Excel 2000/WinNT4.
The macro below looks for a particular phrase on a worksheet,
when it finds it the second time, it selects that cell and the cell
I then want to run a TextToColumns on that selection.
Instead I get:
Run-time error '1004'
Method 'Range' of object '_Global' failed
Thanks for any help.
Cells.Find(What:="TOWN", After:=ActiveCell, LookIn:=xlValues,
:=xlPart, Sea...How to select a mail in the outlook inspector programatically in C#?
I am developing an addins for outlook in C#.
My need is, If I click a custom button on the custom tool bar , a mail
should select automatically in the outlook inspector. Ofcourse I give
some search criteria in the button click event to select the mail.
I can identifiy the mail with my search criteria from the custom
button click event. Means I have mail as outlook mailitem object. But
I am not able to select that mail automatically in the inspector.
Please let me know, if anybody know the way. The code/sample can be
in any programming language.
You mean displa...Outlook 2003 can't select delivery location Exchange Mailbox
I set up an Outlook 2003, added an Exchange Mailbox in the accounts.
Now I got the warning that the default delivery location will be the
Personal Folder. I don't want that and wanted to change the default
delivery location to Mailbox.
However the only option it shows me in this dialog is the PST. :(
The Mailserver is an old Exchange, likely 5.5.
I do have another machine that goes on a Exchange 2003 and there it
works perfekt. So I wonder why the other machine doesn't allow me to
set the mailbox
as the default delivery.
What happens if the delivery is set to PST and I go...Inserting Quotation Marks in all Cells
I have a co-worker who needs to insert quotation marks in the entire
worksheet. It contains both numbers and text. The quotation marks need to
be inserted around each individual cells data.
Can someone help?
Enter this small macro:
Dim r As Range
For Each r In Selection
If IsEmpty(r.Value) Then
r.Value = Chr(34) & r.Value & Chr(34)
Select all or some portion of the worksheet and run the macro. It will put
quote marks before and after the contents of all selected non-empty cells
...Preventing Duplicate Cells
We have a list of about 8600 different numbers which are our accounts. Each
is 16 digits long. We have recently had the problem that when we copy over
account lines, sometimes we create duplicate cells. We need to prevent this
duplication. Is there any way in Excel to prevent the addition of cells that
have the same content?
Check out the 'Advanced Filter' option...
DATA / FILTER / ADVANCED FILTER
You have the ability to copy unique records only to a different location.
> We h...why doesnt my formula cell update automatically ?
=SUM(G5:G11) if I change any cell in the range, the sum is not performed. I
have to select the sum cell, F2 then Enter in order to get the sum to work
Check and make sure that calculation is set to automatic.
"Wozza" <Wozza@discussions.microsoft.com> wrote in message
> =SUM(G5:G11) if I change any cell in the range, the sum is not performed.
> have to select the sum cell, F2 then Enter in order to get the sum to work
Thanks Biff...not sure how it got set to ma...Find date in row and move to cell
I will try and explain this as best as I can but please forgive me if I fail.
I have a date range in row 2 starting in column e and runing through to cell
IR2. I then have a list of tasks in column a staring in row 4.
There is a command button linked to a macro that will insert a new task in
the middle of the existing list of tasks and creates some subtotals etc but
what I would like it also to do is remember the row the active cell is in,
look up todays date in the range and move to that location ready for input.
Any ideas please?
Try the below
Sub ...Extract numeric value from single cell #2
Thanks, thats grea
Landyman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=807
View this thread: http://www.excelforum.com/showthread.php?threadid=26148
...Office 2007 + formatting cells
I have a cell with 6 numbers in for example 098737.
Excel deletes the zeros off from the cell, when i right click format cells
and go to custom i change general to 000000 but it changes it to chinese?
Can anyone help please?
The quickest way to keep the formatting is to put an apostrophe in front of
your number. Like this: '098737
When you hit enter, Excel formats the cell as text and keeps your leading
"Neil Holden" wrote:
> I have a cell with 6 numbers in for example 098737.
> Excel deletes the zeros off from the cel...Select every Nth cell in column? Help please
3 columns A2:C100 contains data. In E2:E14, I want a formula or
function that will get every 3rd cell from Column A, F2:F14 every 3rd
cell from Col B, G2:G14 every 3rdcell from Col C E.g.:
A B C D E F G
Days Week Mon RDays RWeek RMon
1 2 3 7 8 9
4 5 6 4 5 6
7 8 9 9 8 7
10 110 12 3 3 3
1 2 3
4 5 6
7 8 9
12 11 10
9 8 7
6 5 4
3 2 1
3 3 3
I'm guessing Vlookup somehow, but I sure cant figure out how to do it.
Appreciate any help you can provide. Basically, I'm just trying to get
a smaller set of data to work with in Cols E:G versus using the data in
Col A:C which currently i...in excel can I write = c2 as a formula such as =cell(2,3)
Sheet1 is a price list
if a person enters 5 in cell a5 (quantity) I want that 5 to be place in the
line on sheet two. To do so I need to write that cell as a formula. PS I
don't know visual basic and would prefer not to use it.
not really sure what you're trying to do. What else in on sheet 2?
> Sheet1 is a price list
> if a person enters 5 in cell a5 (quantity) I want that 5 to be place
> in the first blank
> line on sheet two. To do so I need to write that cell as a formula.
> PS I don't...