if a cell contains "usable characters". By usable characters i mean i want something to check if a cell contains a number or letter.
cell = AAAAA formula returns true
cell = A123 formula = true
cell = 123 formula = true
cell = ----- formula = false
I think I have a similar problem as this thread http://www.excelforum.com/excel-prog...o-columns.html. The answer is probably somewhere in the forum, but I looked around and I can't figure it out on my own.
Basically, I copied some data off a website and now I am trying to organize it so it is usable. Here is my example. I would like to put the data below into usable columns. The only data I need is the hospital name (column A), location (column B), Births (column C), and Beds (column D). This is how the data currently appears in excel:..............
I am pulling out information from a cell using =RIGHT(C2,4). It gives me 6-2 in the cell, which is what I want. Now I want to sum the cell but how to do it. I have tried converting it but it doesn't work, I tried pulling out each digit, but that didn't work.
Ideally, I would like a formula that could convert cell A1 into a result in one cell.
Attached File : converting text example.xlsx
The code works 100% fine in Excel with no errors when the button is clicked.
However, when it transfers over the data, it is one column off to the left.
Sounds like an easy fix, eh?
Not for me. I've tried everything I could think of.
Code that takes the range and pastes it onto the other sheets in the next available cell:
For Each e In myWs
Set LastR = Sheets("" & e).Range("b" & Rows.Count).End(xlUp).Offset(1)
LastR.Resize(, 2).Value = Range("c4:d4").Value
Next
Set LastR = Nothing
I did find one solution that did bump the cell column offset over 1. It went something like this:
Set LastR = Sheets("" & e).Range("b" & Rows.Count).End(xlUp).Offset(1, 1)
Problem is, it no longer takes it to the next available cell.
It just pastes over the same cells over and over no matter how many times you try it.
I have a row of cells that contain a single letter: W,C,O.
I need to provide a count of the number of occurancies of each letter. So I have a cell that shows me the total W's, a cell showing me total C's and another showing total O's.
Whats easiest way to do this?
I know its not ideal, but on this occassion ive no desire to add and hide another column.
I need a formula that counts the number of cells in a range that only contains one character. Example, column A contains:
Hello
Goodbye
A
B
Hello
Goodbye
In the above, I want the result to be 2. I've tried =countif(a1:a6,len=1) but that doesn't work.
I need to count how many times I've got, for instance, "a" in several cells where I typed some text...
I would need a formula where I can indicate the letter I want and the range of cells where to look at, and having as result how many occurances there are...
If you are very good instead of a single letter, maybe a sequence of letters... but this is an extra!
I have several property codes in column E e.g THA 134,THB 224, C 122, 223 AD. The letters A, B, C, D denote a type of property but do not always appear in the same place in the cell (sometimes 3rd character, sometimes first character etc). I want to put in a formula in column F, so that if the character A, B, C or D is in column E then the result in column F will be "A type", "B Type" etc....
View 9 Replies View RelatedI have random descriptions that have the case dimentions within the text
The descriptions are NOT standard length.
the only thing I can think of is the dimensions in ALL descriptions have this in common:
Length ( x ) Width ( x ) Height
I am looking for the Height value
How can I use the =mid function based on the 2nd ( x ) character?
I'm trying to use logic to identify trends...in each of the strings below, I want to count the occurrences of the left most character appearing consecutively. The answer is to the left
I'm trying to do this via a formula vs. vba if possible
BBBBBBBBBBBBBBBBBBB - 19
BBBDUDUUUUDDBBBBBBB - 3
UUDUDDUUUDUDBBBBBBB - 2
UDUDDUDUUUDDBBBBBBB - 1
[Code] .....
I am trying to build a formula that return what is after ":" For example, in a cell, I have NASDAQ:MSFT and I want another cell to return MSFT. I tried to use the function FIND combined with a LEFT or RIGHT but I could not have it work.
View 2 Replies View RelatedPretty sure this has been asked but have searched the forum to no avail, but I need to extract the numbers from a value which has a letter on the end.
eg. 1000x I need to get out the 1000
or 2p I need the 2
I have sooooo many values ranging in numbers of digits, so just basic left(A1,2) won't work, I'm sure I've seen a search or find function but don't know how to use them!
I need to make a list of Part Numbers in quotation marks. If a number of digits of a P/N is less than 13 a number of space characters has to be added to make the string 13 characters long.
We have example P/N:
1234567890123
12345678
123456
should become:
"1234567890123(no extra space characters should be filled-13chars)"
"12345678(5 extra space characters here)"
"123456(7 extra space characters)"
Is there a function that inputs a cerain number of a specific character ("space" in this example)?
Is there a function in VBA that is similar to either the FIND function or SEARCH function in Excel? The arguments for the FIND function in Excel are FIND(find_text, within_text, [position]).
I have a text string in VBA ("$A1:$D$13") that I want to be able to identify the first "$" and then later the ":". I'm getting tripped up on the 3rd line of code. Thanks a million.
Sub page_set_print_area()
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$13"
x = ActiveSheet.PageSetup.PrintArea
Position$ = Search("$", x, 0)
End Sub
Does such function exist in Excel?
View 9 Replies View RelatedI have special character that I removed with =CLEAN formula.
It was only one character which represents carriage return. It looks like one little square with question mark inside.
After I applied =CLEAN formula it disappeared, but now I don't have space between these two words.
How could I replace this special character with space?
I want to use the find function on a range and search for a "," character. If I get a "," I want to return a True otherwise false.
I tried just recording a macro using the find function but no code shows up.
how to use the find feature in a macro, can this be done?
excel 2010
I want to count from each cell that doesn't contain "0". So if cell C2=100, I want to be able to count the number g1*2 from that cell and return a value. But then I want to start another count from c5 to the number of g1*2 and then another count from c8 etc basically any cell that contains a value other than "0", I want to start a count from.
The point of this is that the half life will expire after that count, so I want to be able to add the drug levels on an ongoing basis until the count of the half life has been reached. But there will be further dosing along the way before this half life is reached and these values need to be added to the existing value until the half life expires.
I am trying to count data using several criteria
1.Need to add data from for a certain category, say "blue" + data during a certain date but exclue the ones with zeros
My formula using arrays look like this, but it is still counting data with zero in the cell as an item
=count(if(A4:A400(text by category)=”blue”,count(if(O4:O400(date)<”04/01/07”,count(An4:An400)[Actual data],-(countif(An4:An400,0))))))
To all sifus out there, how can i transfer from these:
NAS517-3-2
-41353913
NAS517-3-5
NAS517-3-4
-42MS27253-2
-43353908
-44357182
To these:
NAS517-3-2
353913
NAS517-3-5
NAS517-3-4
MS27253-2
353908
357182
I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?
Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.
I have a cell which will contain SER01+SER02+SER03
and what i need it to contain is [SER01]+[SER02]+[SER03]
and shocker is i've got this to work for the first instance but not the other two
code as below... be grateful for your help
Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub
I would like a formula in cell B13 which would look at the range B2:B10 and count how many are in between 07:45-08:00. In cell B14 I would like it to look at the same range and say who is on shift between 08:00 and 09:00, and so on. The reults should be the same as I have typed in cells B3-B29. Is this poosible and/or is there an easier way if there is?
I have one long column filled with the names of people. I need to count the number of people that show up once and the people that show up more than once. So I am looking for any name that shows up more than once or just once and not the specific name of the person. I can use two different formulas for each result I am looking for.
For instance(column):
Patel
Patel
Patel
Smith
Myers
Kaplan
Jones
Jones
I need to come up with an answer that shows that 2 people have their names show up more than once and that 3 peoples names show up once.
Would this be a good example to use the count function??? if so could you get me started?
I have a list that have the same items in it and all I want to do is only show them once but to add up the values in each.
Never used it and playing with the idea to replace a pivot table
I have a list of names and what type of call they have done.
in total there is 7 different people and 2 types of call types, inbound and outbound.
I want a table that will show the Agents name and how many inbound and how many outbound calls that agent has done.
So I want 3 coumns, name, inbound calls, outbound calls.
Right now I have a column of about 300 rows where I want to count how many actual names are in the column. I thought that COUNTA(c2:c313) would work, but it returned 312, while Im expecting it to return about 10.
I think that the reason is because all of the cells in this column are pulling data from another sheet using this formula: =IF(Clients!I299="","",Clients!I299). Most of the data are blank cells
Since of the rows in this column are empty, so I was wondering if I perhaps there was a function such as "DONTCOUNTIF" which would count the cells which didn't have a certain value, in this case "".
I have a range of cells that may contain several different abbreviations (text groups). I have used the COUNTIF function with * place in front of text to count cells that contain the listed text eg =(COUNTIF(G6:G39,"*HWD")) which has worked well and returns an accurate count of the cells that contain HWD. I have tried to do the same thing for a COUNT function that contains an IF function and no count is returned. When I remove the * from the formula a count is returned for cells that contain only the HWD text and not cells that contain HWD and other text. {=COUNT(IF(P9:P69="MCAW",IF(G9:G69="HWD",J9:J69)))}.
View 3 Replies View RelatedI am creating a spreadsheet to keep track of money and grouping them into categories. I have a column to group a purchase into a category (column E) and the amount spent in column F. I want column T to add up all of the purchase that were made according to the category in Column E.
View 2 Replies View RelatedWhat i am trying to do is count the number of rows that contain data.
It will always start in A7 and when creating the macro i hit control-shift-down arrow.
There will be a differnt number of rows for each data set. When i ran the macro it kept the result of the first data set that i used to create the macro.
here is the