I have a spreadsheet that shows a dash in some blank cells and I can't get rid of it. Cells are formatted as general, there's no event code, no conditional formatting, no data validation, no auto-formatting, no nothing. I can copy a cell from outside the range with no dash in it and paste it into one of the cells with the dash and the dash remains. I can't figure it out.
View 9 Repliessection 100
section 200
100-1
200-1
I have a large spreadsheet with text, numbers and blank cells. Is there a code a can use to make all blank cell have a dash (-) without affecting the other cell with data.
I have a row of 2900 single letter (middle initals) however 222 users have no middle inital. this is a password scheme and need 7 digits, without the middle inital i only have 6. so I want to replace all 222 cell that are blank with a dash can this be done without doing each by hand?
View 4 Replies View RelatedIn excel if you press F5Special, you will be given to select cells with some criteria but there is no criteria to select cells with zero value in a selected range. Is any such option is available in excel or a macro is needed for the same?
View 9 Replies View RelatedOk so my named range looks like this:
[Code]....
However, I want to ignore the "" cells and the truly blank cells... However, I think all of them will have "" since I have this formula in all of the ones I'm putting in the range:
[Code]....
How do I go about getting these results into a named range so I can use it on validation since validation only seems to ignore truly blank cells and not the "" ones.
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies View RelatedI have sporadic cell values in a column. I will be describing the lamen logic I use to fill in these blanks manually. Find the first nonblank cell in the column, then look for the next nonblank cell in the column. I count the number of blank cells in between. If even # of blank cells, then I give the first half of the blanks the value of the first nonblank cell and the second half of the blank cells the value of the next nonblanck. If odd # of blank cells, then I do the same with the exception that the odd cell that falls in the middle will be randomly designated the value of the either first or second nonblank cell.
View 4 Replies View RelatedI can count the blank cells withiin a range using
=COUNTBLANK(C6:AD2506)
But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.
It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.
I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.
View 8 Replies View RelatedI presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.
What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).
This all needs to be done in Arial, 10pt, white.
Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them, and
my count changes. So, can I get Excel to put a value into my "blank" cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).
This is what I'm looking at:
A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")
In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C.
I have a list that looks something like this:
Column B
Row 4 Item 1
Row 5 Item 2
Row 6
Row 7
Row 8 Item 3
Row 9
Row 10 Item 4
Row 11
Row 12
Row 13
Row 14 Item 5
The range of cells in column B containing the items has a name "ColStreams"
I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.
The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.
=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))
I work in the Oil and Gas industry and deal with large data tables that have "Location Codes" for lack of a better term.
The problem is that not all Databases use exactly the same format of code, they will however always have the following:
xx-xx-xx-xx ---> ie. a string of four numbers each seperated by a Dash.
This string sometimes will appear after a text name of varying length. With this in mind I would like to parse the four numbers into four different columns.
Things I've tried with some success:
Using the "Right" function to break off the location code off the end of the entire name, and then using the ".parse" command in VB to seperate the numbers at given intervals.
This is great for certain purposes however because the four numbers are not always 2 digits, and the ".parse" command cuts at given intervals it is hard to accurately parse an entire set of data.
Some examples of location Codes are given below:
9-23-78-11
WEST DOE 11-16-81-15
KNOPCIK 3-9-73-9
[URL]...
This entire link is in one cell i would like the data Iowa_Health_System
What formula can i use
I recently saw Krishnakumar use the formula.
= sumproduct(--(a2:a10=d2),--(b2:b10=e2),c2:c10)
I haven't seen sumproduct used this way. Very cool. I see how it is working, but can anyone tell me more about the syntax of the dash dash used in array 1 and array 2? I have never seen this. How is it different than =SUMPRODUCT(1*(A1:A9=G1),1*(B1:B9=H1),C1:C9), which is the syntax I'm used to seeing.
the situation is i have a sheet, contains like 500 cells, from A1, to T500, some of the cells are blank, then i figure that from cell A1 to E500 not all the words in capital, so i make it with upper on the2nd sheet, then, i copy + paste value back to the first sheet (A1 to E500)
i get wht i want, but why it's like there's no blank cells anymore on my worksheet.
like you know, if u want to jump from a filled cell to the blank cell, you would hit Ctrl + Down, it jump to the very end of the datas that i paste value before.
so when i hit Ctrl + Down from A1, it jumps to A500, meanwhile the filled cells only 10 cells.
I have a spreadsheet with data in 7 columns. Columns A-d have one line of data in them but the other 3 columns have more than one line.
How do I write a macro which will copy the first row of the first 4 columns down until I reach an empty row?
I was curious if it is possible to give a single cell multiple number formats based on what the number is in the cell. So for example if my number is bigger than 1000, I would like to use comas so that it looks like this 1,000. However, if it is less than 1000 I would like it to look more like this 999.00.
View 6 Replies View RelatedI have a column of data that may or may not have a formula in them. The cells are formatted in Accounting, w/o the leading $ sign. (i.e. 133.57) The value zero shows up as a hyphen or dash.
The issue is the format of the cell. In that attached worksheet, i inserted two formulas in cell b6 and b7. Both formulas are exactly the same, but the formatting is different. One is in the accounting format, but the other is in general. When I run the code, only the general formatted cell gets found.
My real worksheet is in the accounting formatted code, so I don't want to change my worksheet's numbers into a general format. How I can find the zero value thats in an accounting format?
Below is a strip down version of the code.
VB:
Sub test()[COLOR=#333333]
[/COLOR] Dim rLookInADR As Range
Dim foundcell As Range
Set rLookInADR = Range("b1:b380")
Set foundcell = rLookInADR.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
MsgBox (foundcell.Row)
End Sub
[COLOR=#333333][/COLOR]
Here is what my worksheets looks like.
54841
54841-1
54841-2
54841-3
55654
58850
58850-1
53254
53254-1
53254-2
56696
56696-3
56696-4
53214
54789
So this list of information needs to be changed to the following
54841
54841-1
54841-2
54841-3
55654
58850
58850-1
53254
53254-1
53254-2
56696
56696-3
56696-4
53214
54789
All the numbers in red need to be cleared. No I have been able to find the original number and clear it. For example I can have the program find 53254 and clear it, but I am really having a tough time having it find -1, -2, -3 and so on. If the program finds a -1, I need any dashes after that to be cleared. If the program doesn't find a -1, then it goes to -2. If it finds a -2 then any number after that needs to be cleared. Here is the code I have to clear some of the numbers.
I know this works because I have seen it in a workbook at a client's office.
I need a custon number format to substiture an "em" dash for zero. An em dash is a hyphen with the width a the letter m. There is also the so called en dash tht is the width the the letter n. A search of the internet shows that to create an em dash in a cell that contains a 0 ("zero"), hold down the alt key and type 0151 on the numeric keypad. Typing 0150 insert a en dash.
I want to be able to set this up as a custom number format in the custom number dialog.
I have some rows that have some text for example "0- 56933 tex". the numbers are not always the same, but there is a dash, and sometimes there is no space after the dash. I want get the numbers before the dash in a variable and the one after in another variable.
View 9 Replies View RelatedI have entries that look like this - 300-7188258-1
300-8983980-5
I need to remove the dashes. I find and replace all - with nothing. that has worked for YEARS. Seems simple to me.
Today, when I do it, Excel leaves the "300" and removes everything else. I even did a CLEAN function and it still does it.
How to remove a "-" out of first two letters of a sentence. if there is a "-"
for example:
BEFORE: Red-Blue Shirt With Ribbon
AFTER: Red Blue Shirt With Ribbon
However, if there is not a "-" in the first two letters then it not change anything.
For example --nothing changes:
BEFORE: Red Blue Shirt With Ribbon
AFTER: Red Blue Shirt With Ribbon
i am trying to isolate everthing after the last dash, now i did the same with the first dash using left, i tried to do the same using Right to get "155", but it gives me incorrect result,
Sheet1 ABC887878-1554-155 87878987878-1554-155 554-155Spreadsheet FormulasCellFormulaC8=LEFT(A8,FIND("-",A8)-1)C9=RIGHT(A9,FIND("-",A9)+1) Excel tables to the web >> Excel Jeanie HTML 4
I am trying to create a simple dash board using only Excel and no VBA. What I am trying to do is I want to create dependent drop downs and link up drops downs that will pull out the required data individually or with combination of those two. For example, let's say
A column has Dates of transaction,
B column has Item name
C column has Units
D column has Month name
E column has Shifts (Morning or Evening)
I am trying to make a dash board that will pull total Sum of individual items based on
Only Date wise
Datewise + shift wise
Month wise
monthwise + shift wise
I managed to create drop down list for dates, months, and shifts but i dont know the way how to link the individual drop down lists together to pull the data based on the selection. which function i should use here and how?
I want to know how many emails are in my list. There is dash sign where the emal id is not available. The subtotal formula gives me the total entries in range but I want to know only email count numbers.
In my range there are 6 entries and only 4 email ids. Now I'm getting output 6 but I want it 4 ignoring 2 dash signs.
I need a formula that will extract only the text between the 1st and 2nd dash in a cell. Example:
10466193-1909457-
10648145-3026718-t526363536m
This formula would return only the following:
1909457
3026718
We often get spreadsheets from our customer that are formatted with both comma and dash combinations. We would like to only have the comma seperation.
Example:
R1, R2, R3-R5, R30
Result:
R1, R2, R3, R4, R5, R30
There is usually only one alpha character but sometimes more. Example: CR1, CR2, CR3-CR5.
The following macro works great if there are no alpha characters. how to solve the alpha/numeric combination?
Function Nums(rng As Range) As String
Dim adnum As Integer, n As Integer, num, Txt As String
num = Split(rng, ",")
[Code]....