Explanation For Formula That Counts Number Of Numeric Characters
Nov 21, 2012
The formula below was posted recently as a way to extract a numeric substring from somewhere in larger string. When I first saw this formula, how it "came up with the goods" and so I spent some time looking at parts of it to see what they did.
Although I've made some progress, I still don't fully understand it. Heres the formula, and below Ive detailed how far I've got:
=Iferror(lookup(9.99e+307,--mid(a1,min(find({1,2,3,4,5,6,7,8,9,0},a1&1234567890)),row(indirect("1:"&len(a1))))),0)
I hope my findings below are correct:
1. The formula is returning the first substring of contiguous numeric characters from the full string in Cell A1
2. 9.99e + 307 ensures that the search value won't be exceeded by the returned value.
3. The Mid function uses Min(Find( to get the character position of the first numeric in A1 (so MID then has its first 2 parameters). So far so good.
4. Now... I cant work out where MID parameter 3 (the substring length) comes from. I suspect its the row function, but can't see how as this isn't an array formula, and without CTRL+Shift+ Enter, I could only get Row to return 1 in my experiments (giving me only the first substring digit).
How does the formula return the correct number of numeric characters, (apparently) without counting them?
View 3 Replies
ADVERTISEMENT
Dec 7, 2006
I am trying to write a formula that will recgonise either text or numicial value as the result is used with a match formula. In column C I have data as follows:
1400 SBY
1230 9985
ADO
I am using a =--RIGHT(C4,4) formula in column E to get the required data and then using my match formula to extract other data. How can I rewrite the above formula so that it can read either text or numbers that will allow my match formula to work.
View 5 Replies
View Related
Jun 17, 2009
I have an array formula which reads:
{=SUM(IF(Female2!$A$2:$A$5000=$A403,IF(Female2!$C$2:$C$5000=C$402,IF(Female2!$1:$1=C$401,1,0))))}
However this formula only counts the number of cells (returns 96 for 96 cells) rather than adding the numbers in those cells to come to a total of 34.
View 4 Replies
View Related
Nov 6, 2013
I have been using this function to count the number of unique text values in a data set:
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))
It works great if I want to count number of unique text values overall. However, I want to count the occurrences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).
View 7 Replies
View Related
Jul 20, 2009
I came across this formula but could not for the heck of it understand what exactly does it do. I will really appreciate if some one can make me understand what this formula is intended to do.
=Countif(data,data)
where data is the named range..
View 9 Replies
View Related
Aug 6, 2008
I have a formula in a spreadsheet set up by someone else and it says. =HLOOKUP(C$30,J$2:$N$259,ROW()-ROW($2:$2)+1,). Can some one explain what it is doing in the bolded part? I understand basic vlookup and hlookups but not what this is doing. I have looked at the help but it only says that it returns the row numbers as reference but what does that mean. - and what does the "+1," and the comma at end mean.
View 2 Replies
View Related
Jan 13, 2007
I have a cell that contains BYO CUSTOMER XYZ 76458992
I want the cell next to it to remove all of the letters and display only 76458992.
View 9 Replies
View Related
Jul 1, 2009
I have a column contains Postal Adress in more than 5000 rows. Column contains Door Number, Area, City and Zip code. I need to separate "Zip code" alone in next coulmn. Zip code (of India) will be in six digits like "600083" also some cases contain space in middle of zip code like "600 083" (after 3 digits). Is there any way to do this without doing cut & paste?
View 9 Replies
View Related
Jul 3, 2009
I have a column contains Telephone numbers with or without area code and country code for across the country (India). I need to extract the telephone numbers alone (neither area code nor country code). Telephone numbers will be 6 or 7 or 8 digits (not more than that). Is it possible to extract any set of numbers contain 6 or 7 or 8 digits continously? Some of the cases contains 2 contact numbers (2 set of 6 or 7 or 8 digit characters, between special characters will be there like slash, comma, space, hypen, etc.,
Here are some examples: ....
View 9 Replies
View Related
Aug 10, 2007
In my column of text strings, I have a multiple format of strings make-up. Below is just one of them I have to check that the first 6 are digits from 0 to 9, and it is followed by a hypen. If condition is true, the first 6 digits is the output (ie. 345678).
345678-S
=IF(ISNUMBER(--(MID(A1, FIND("-",A1)-1,1))--(MID(A1,FIND("-",A1)-2,1))--(MID(A1,FIND("-",A1)-3,1))--(MID(A1,FIND("-",A1)-4,1))--(MID(A1,FIND("-",A1)-5,1))--(MID(A1,FIND("-",A1)-6,1))),LEFT(A1,FIND("-",A1)-1))
However, if I were to continue doing this for other strings, I would soon run out of characters limit that is allowed in a cell. I wonder whether a formulae such as below is valid? Any suggestion or help for a shorter formula is very much appreciated.
=IF(ISNUMBER(--(LEFT(A1,1+1+1+1+1+1))),LEFT(A1,6))
View 4 Replies
View Related
Oct 16, 2007
The macro I have select 2 columns and 2000 rows. I need a VBA code that will loop through each of these 4000 cells and remove all characters (replace them with blanks) that are not a number, a period or a decimal. Characters from other languages like Chinese, Japanese and Russian should also be removed.
View 3 Replies
View Related
Feb 24, 2009
I have something simple (i think) but i cant think it through. I have a range of cells that are populated by a link and so even though the value shows #,### It really shows #,###.########
I would like to trim everything after the decimal point without having to adjust format of the range since some cells are general format and others are Percentage format but they all suffer from .####### HOWEVER, the last digits are variable and never the exact same amount of characters.
for each cell in range("C4:J9")
if cell.value "" then
Trim(Left(cell,4))
end if
next cell
View 9 Replies
View Related
Apr 9, 2014
I'm working with a large list of items of varying length. A sample worksheet is attached. I need to do the following:
1) If the characters after the final forward slash from the right are of the pattern "tttxxx" (where "t" is text and "x" is a number/digit), then return all characters to the right of the final slash, including the slash itself:
Y:/Series/hun/hun109 ----> /hun109
2) if the characters after the final slash from the right are not of the pattern "tttxxx", then return all characters after the second slash from the right, including the slash:
Z:/Series/crd/crd409/editorial ----> /crd409/editorial
3) if there are less than 3 backslashes in the item, return a blank.
View 3 Replies
View Related
Jan 10, 2014
I have a list of numbers in which I need to return the last 5 digits; the length of the string may vary.
However, if the last two digits of the number ends in a specific value then it should skip over the last two numbers and return the preceding 5 digits.
I have tried and have used the very basic Left, Right and Mid functions. My problem is I dont know how to code the formula to identify the last two digits and skip over them, if necessary.
I have provided an attached example.
Extract numeric value based upon ending characters.xlsm
View 5 Replies
View Related
Feb 16, 2010
I thought I had this solved but an inconsistency has shown up. I have a long list of chemical formulas that I want to format (partially) as subscript.
Basically what I need the macro to do is look at each character within a cell and check to see if it is numeric. If it is AND it follows a non-numeric character it should be formatted as subscript.
Examples
H2O the 2 should be subscript
H2SO4 the 2 and the 4 should be subscript
2CCl4 only the 4 should be subscript
View 2 Replies
View Related
Feb 16, 2010
I have a long list of chemical formulas that I want to format (partially) as subscript.
Basically what I need the macro to do is look at each character within a cell and check to see if it is numeric. If it is AND it follows a non-numeric character it should be formatted as subscript.
Examples
H2O the 2 should be subscript
H2SO4 the 2 and the 4 should be subscript
2CCl4 only the 4 should be subscript
CuSO4 - 5H2O
View 2 Replies
View Related
Jun 10, 2013
I am trying to see if there is a way using count or another option, of using one formula to look in a column and then spit out how many of each item are there:
i.e: lets say it says a, b, c, d etc...
Can a formula go through and say you have 3 of a, 5 of b and so on
I understand the basic of count sum iserror etc, just not sure if excel can do what I am wanting or if i should use access.
View 2 Replies
View Related
Oct 8, 2009
I would like to sort words or data which has the First capital letter, words which has spaces and words which contains number... i have attached a sample file.
View 9 Replies
View Related
Jan 13, 2009
in sheet 2 cell g16 i need it to count how many times "horse racing" appears in sheet 1 column m and also for it to count how many "y" are in column g of sheet 1 and return something like
4 from 4
this example is taken from the attached sheet
basically it needs to count the yesses in column g that correspond to the horse racing in column n
so there could be 7 instances of horse racing ( 4 in this example ) and count the y's in column g which could be 2 ( 4 in this example ).
View 6 Replies
View Related
Jul 5, 2014
I need a code that search through a column, counts the numer of times a string occurs and creates on another sheet the unique string and the number of times it occured.
e.g
I have this columns in a worksheet
s/n name company trainer
1 ball Dell Mk
2 doll Msed Kl
3 kol Dlink Mk
4 ball Msed Cl
5 Koll Dell Gl
I need the result to appear on another worksheet like this
Name NumberofTraining
Ball 2
doll 1
Koll 2
View 9 Replies
View Related
Aug 30, 2009
=LOOKUP($C1,$A1:$A1,$B1:$B1)
Though i wanted to rid the Na error from it when C is blank
I seen a few ways, And wanted to try the method of using Countif becouse it can be used withen the same cell without using extra cells like another method i seen "=IF(ISNA(A1),0,A1)"
what i know of countif
This function counts the number of items which match criteria set by the user.
=IF(COUNTIF($A1,"")),LOOKUP($C1,$A1:$A1,$B1:$B1),"")
so how the heck can the countif function be used to rid the NA of a lookup. i just cant see a relationship between counting a lookup.
View 5 Replies
View Related
Jun 18, 2007
In cell BI38 I have a function which counts the number of F's in the range AN38:AY38. I want the cell AM38 to turn blue if the value in BI38 >=3. Is there a simple way to do this?
View 8 Replies
View Related
Jan 25, 2008
I'm trying to create a work Rota and I'm having a bit of a problem with a certain section. In Worksheet 1 I have the following headings:
Cell A - Name
Cell B - Monday
Cell C - Tuesday
Cell D - Wednesday
etc
Under these headings is each member of staff and the hours they work, IE L (Late), E (Early), SD (Short Day) etc. In the final Column, it counts the number of hours that this person works (Early is 7.5). In Rows 46, there is a section here to work out how many people are working earlys, which is where I have the problem.
The hours are worked out by doing a lookup function on the cell that says E, L etc and goes to a CODES sheet and pulls the value of that letter. What I am wanting to do is lookup that Letter, which and look in the cell next to it and count how many people would be on an early etc.
View 5 Replies
View Related
Dec 19, 2013
I have data over the last 170 days. Each entry has a time stamp. I would like to count how many entries there are between 7:45 am and 2:45pm. A total count across all 170 days is needed, but it would be great if I could break that down by calendar day (i do have that as a column).
Here is a sample of the data:Friday11/15/1307:08AM
Friday11/15/1307:08AM
Friday11/15/1307:16AM
Friday11/15/1307:16AM
Friday11/15/1307:46AM
Friday11/15/1307:46AM
And the sheet has dates starting in June and ending in mid December.
View 14 Replies
View Related
Jul 18, 2009
I'm hoping there's a more efficient way to do this in that I'd like to replace - for example - over 100 formulas with just 1.
Take this simple example range in a column of numbers: 100 down to 0 and for each cell there's a count of how many times that number is greater than those that follow. The 1st is 100 times, the 2nd 99 times and so on... finally I need a sum of these resulting counts.
My current method would require over 100 formulas in 100 extra cells to do this
View 9 Replies
View Related
Oct 8, 2009
I have three companies in column A, In Column B I have a date of when an item was sent to them, in Column C I have the amount of days this item has been out with that company. Is there a formula that will count the number of days an item has been out with one of the companies per month, and find an average if more than 1 item has been sent per month. Two items have been sent to company A in Sep 09, one item has been there 10 days the other 20 days, the answer I would be looking for is 15.
View 4 Replies
View Related
Feb 4, 2007
i know its out there, i just cant find it. I have a list of names in a specific pattern of cells on a spreadsheet. I would like excel to give me a number of how many names i have in this spreadsheet. I know COUNT does numbers, but is there a formula that counts names?
View 2 Replies
View Related
Sep 14, 2013
I have a range that contains a mixture of hard coded data and data pulled from a database sheet. I want to average only the hard coded data not the ones in the cells containing formula. I need this to be dynamic instead of just selecting the cells as formula might be added later on.
View 1 Replies
View Related
Apr 25, 2013
I am working on a sheet that has over 150 tabs of information. It is a simple audit that allows for Yes, No or N/A to be selected for each audit category. I have an Outcome Summary tab that is pulling totals for all the audit information on each tab. I did a CountA statement to count any cells (from all worksheets) that had anything selected, so I have my count for the amount of files audited, but for the life of me I cannot come up with a formula that counts the ones that passed or failed the audit. Needs a formula that counts for Yes as 1, No as 0, N/A as 1 (these don't count against us) and doesn't return an error for a tab that wasn't selected.
View 3 Replies
View Related
Oct 4, 2011
I need to get together a countif formula that only counts if BOTH specified criteria are true... This is what I thought would work:
Code:
=COUNTIF(W1:W28, AND(>A18,
View 4 Replies
View Related
