Mid() Range #Value
I have a column A1:A5 which holds text format:
15:00  18:30
07:30  21:00
13:00  14:00
19:30  20:30
20:30  23:30
=SUMPRODUCT(MID(A1:A5;9;2)+0+MID(A1:A5;12;2)/60MID(A1:A5;1;2)+0MID(A1:A5;4;2)/60)
I use the above formula to calculate the total hours in the 5 timespans (in this case 22 hours). It can probably be shortended but remember im just learning this.
It works, but my problem is that once in a while one of the cells A1:A5 will be empty, or hold a text string with no numbers. When this happens my formula returns #Value!.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
 Allow User To Select Range Midmacro
 About Sumif And Mid
 MID Function
 Mid And Find The Value
 Trim / Mid / Right Etc
 Using MID In VBA
 Using IF Statement With MID
 Find/Mid Formula
 Use Mid To Extract String
 Advanced Type Of Mid
 Mid Substitute Formula
 Reverse MID  Search From Right
 Shortening Text With MID
 Use Mid Function In Vlookup
 Splitting Text Using MID, RIGHT Functions
 Extracting Mid Strings From A Cell
 Calculate STDEV For Mid95% Of Data
 LEN And MID Formulas With Numbers
 Mid Find First Capital Letter
 Average Top Mid And Bottom
 Function Similar To LEFT / MID / RIGHT
 Applying A Mid Function To Many Rows
 Inserting Text In Mid String
 How To Use Mid Function Based On The 2nd ( X ) Character
 Correct Approach? (Multiple MID Functions)
 Left And Mid Functions Inside Subtotal
 Mid Function Returns Odd Results
 Using MID And Stoping When You Hit More Than A Single Space
 MID Function Formula String Extract
 MID Function :: Numeric, Alphanumeric Or Alpha
 Use FIND And MID Function For Extract From String
 Mid, Left, Right Function Macro Error
 Random 3 Letter Prefix Form (left, Right, Mid)
 Conditional Format For Low, Mid & High
 MID FIND LEN (return The Matching Word In V6)
 Storing/Copying/Pasting MID Function Output
 Sort Rows By Mid() String Of Column Cells
 Mid Function :: Match Account Number With Description
 Move Mid Text In Cell To Front. Reverse
 Substitute Function With Find, Mid & Left
 Convert Mid & Find Function To Vba
 MID Formula: Extract Only The Text Between The 1st And 2nd Dash In A Cell
 Functions Left, Right, Mid And Trim: Early Binding And Late Binding
 Got A "Mid": Copy A Value To A Refer Contained Within Another Cell
 SUMIFS Function In 2007 (range To Sum, Range To Evaluate, Evaluation Criterion)
 Function To Sort A Range By The Values In A Specific Column And Return The Range
 Get The Range Address Of A Dynamically Named Range That Refers To A Formula In VBA
 Dynamic Named Range Sort Including Cells Outside Defined Range
 Find Dates Between Monthly Range And Sum Another Cells Results That Are In A Range
 Application.inputbox: Range To Copy And Paste The Range's Link And Format To A Different Sheet
Allow User To Select Range Midmacro
I have a protected worksheet with a mixture of locked and unlocked cells, and I'd like to provide an option to allow the user to select a large range of mixed (locked and unlocked) cells and, in the unlocked cells only, prepopulate with a standard formula. I've got most of it working, but what I need is the specific code that allows the user to specify the range of cells they want the macro to act on.
View Replies!
View Related
About Sumif And Mid
I got a column A containing a alphanumeric data and want to sum up the numeric value of each cell which begins with "t" and have the sum divided by 486 (i.e. (120+230+25)/486). I tried the following formula but to no avail. A t120 t230 m45 m30 t25 My formular: sumif(mid(a1:a5,2,3,">0"))
View Replies!
View Related
MID Function
I have a column wiht a series of characters like this: ABCDEF+966789 The column is repeated like this all the way down but with different numbers. How do I correctly use a formula like this: =MID(A1,5,255) I only want the characters on the right side to show up in the row.
View Replies!
View Related
Mid And Find The Value
the value is not found it gives me "Value error". it picks up another line which I don't want ie.. From 200903  To:200904. =MID(K9,FIND(":",K9)+2,FIND("",K9)2FIND(":",K9)) or =+MID(L9,SEARCH(":",L9)+2,SEARCH("",L9)SEARCH(":",L9)2 PROJECT NUMBER : 353089  NORTHERN ALTA INSTITUTUE OF TECH FROM : 200903  To : 200904 Result: 353089 200904 (Don't want this to be picked up) # Value (Instead of # Value error I want to have 0 show up)
View Replies!
View Related
Trim / Mid / Right Etc
Sample of Column A: Sat 15th Sep 07 14:15 Bath Rugby 29  15 Worcester Warriors Recreation Ground 10,010 Sat 15th Sep 07 15:30 Harlequins 35  27 London Irish Twickenham 39,400 Report Sat 15th Sep 07 18:00 London Wasps 19  29 Saracens Twickenham 39,400 Report Sun 16th Sep 07 15:00 Bristol Rugby 13  26 Leicester Tigers Memorial Stadium 8,125 Sun 16th Sep 07 15:00 Leeds Carnegie 24  49 Gloucester Rugby Headingley Carnegie Sun 16th Sep 07 15:00 Newcastle Falcons 33  12 Sale Sharks Kingston Park 5,859 Report Trying to display in column B and C: Bath Rugby Worcester Warriors Harlequins London Irish London Wasps Saracens etc.... Because of the varying length of text each time, i'm having problems doing this. Tried =MID(A1,FIND(":",A1)+4,25) but obviously get extra text other than team name.
View Replies!
View Related
Using MID In VBA
I know that I must be doing the following wrong, is there a way to do it? I don't understand why I can't use equal with mid. cv = activecell.value a = mid(cv,10,2) if a = 50 then msgbox "Error"
View Replies!
View Related
Using IF Statement With MID
how to use IF and MID functions together. =IF MID(B2,4,1) =5 Then return value 5, if it =1 then return value "1.75" etc. Is this possible? Also i have 5 other fields that will update based on the returned value. Will that cause errors?
View Replies!
View Related
Find/Mid Formula
I need a formula created that will enable me to find a number within a text and then report the number in another cell. Example texts are: "unplanned absences (not work related) (8)" and "Working days (YTD) (53)" I was thinking of doing a find/mid formula but I am unsure if this would be best and how to do it. I would want it to find the number 8 or 53 in the text examples above
View Replies!
View Related
Advanced Type Of Mid
Assume cell A1 is "ageioslop315555555138lkeameox". Is there a formula that I could use that could match 31555555138, and if it matches put the 31555555138 in cell A2. You can probably see where I am going with this, there is several thousand lines that has sporadic descriptions that I need to find out which ones have certain #'s that correspond with info. on our end.
View Replies!
View Related
Mid Substitute Formula
I have data coes that need to be converted, basically need to remove 1st and 12th digits, 12th digit only, or 11th digit. I have built spreadsheet with a mid sub formula to do all 3 separately, but cannot figure out how to combine the formula to do all three. I am attaching the spreadsheet,
View Replies!
View Related
Reverse MID  Search From Right
I need a formula that would search a string in a cell for spaces starting from the right. similar to MID but with the start and end pos reversed. eg. col A below stores the text. col B to D is where i need to write the formulaes and drop down for the number of rows in A (in this case 3) A 33 444 555 444 55 666 666 777 98 88 here is what the result in cols B C and D should be after the formula and drop down: B 555 666 88..................
View Replies!
View Related
Shortening Text With MID
I'm using this function to make a short description of a product from a long description. =MID(K2,1,200)&"…" So it's taking the first 200 characters of cell K2 and adding a ... to it. Whats happening is it is splitting words and ending at character 200 instead of the end of the closest word to character 200. Is there a way to alter this formula to break at the end of a word close to character 200? Or is there a better way to do this?
View Replies!
View Related
Use Mid Function In Vlookup
using the Mid function in Vlookup. I want to get the mid of the value which i get using vlookup. Example: if my Vlookup gives the value as "GSC 03Parts & Service SystemsGSMSTest " i want to use the mid function to get the a new value as 03. I'm using the below
View Replies!
View Related
Splitting Text Using MID, RIGHT Functions
I have some samples below and I need to split them up. CritValsMtrx(1, 1) = 8850216 CritValsMtrx(1, 2) = 10 CritValsMtrx(2, 1) = 8850832 CritValsMtrx(2, 2) = 5 The MID function to split the text as below and it works. MID(A1,1,FIND(" (",A1,1)1) returns "CritValMtrx" I have tried the RIGHT function to split the text to have the followings but it didn't work RIGHT(A1,1,FIND(",",A1,1)1). "1)8850216" "2) = 10" "1) = 8850832" "2) = 5" In a nut shell, all I need is the text from the comma to the end of the string.
View Replies!
View Related
Calculate STDEV For Mid95% Of Data
I have a worksheet containing data in columns. For each column, I would like to calculate the standard deviation of the mid95% of the values i.e. 2.5th percentile to 97.5th percentile (to reduce the effect of "outliers"). So first of all I calculate the values of the 2.5th and 97.5th percentiles: A102 (2.5th percentile) = PERCENTILE(A1:A100,0.025) A103 (97.5th percentile) = PERCENTILE(A1:A100,0.975) Then using an array formula shamelessly plagiarized from this very website, I calculate the standard deviation of the values that lie in that range: {=STDEV(IF((A1:A100>=A102)*(A1:A100
View Replies!
View Related
LEN And MID Formulas With Numbers
I am trying to be able enter an a dollar amount into one cell and have the numbers separate into their own cells further down the page. It works basically but if I have room below to hold 10000000.00 and I only enter 100.00, the output looks something like #######100.00. I have an IF formula using LEN and MID formulas in each of the cells. So if nothing is entered, a VALUE error appears in each cell. Is there a way around this? I will paste the formula and the Excel screenshot using the lovely HTML maker below: ...
View Replies!
View Related
Average Top Mid And Bottom
I am needing to do a formula based on a range of data that will return the top third middle third and bottom third. Example Data W X Y Gross Gross Margin Margin% Rank 51,241 36.46%2 25,584 19.56%20 19,914 16.26%40 17,188 14.27%52 19,167 15.55%43 6,828 5.23%84 48,076 29.17%3 18,282 13.78%47 16,896 12.75%58 16,124 12.14%64 16,549 12.27%59.................... I first need it to look in the rank column for highest number and divide by 3 Then I need it to give me the average Gross Margin if rank is between 132, 3364, 6595 I have this for the top and bottom but can't get the mid range to work(except it doesn't automatically calculate) SUMIF($Y$8:$Y$102,"<33",$W$8:$W$102)/32 I have tried averageif.... Using rank to calculate this may not be the most efficent way
View Replies!
View Related
Applying A Mid Function To Many Rows
I need to cut off addresses by 30 characters. I am doing this using the MID function. However, being a newb, how can I apply this function to say D4, D5 all the way to D899, without copy and pasting for 800 times? I did search this first, and couldn't find a resolution.
View Replies!
View Related
How To Use Mid Function Based On The 2nd ( X ) Character
I 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?
View Replies!
View Related
Correct Approach? (Multiple MID Functions)
* I have a complex Excel workbook with several worksheets. * The first worksheet is for raw data, which consists of a single string of data, separated by spaces, to be uploaded to a mainframe. There is no consistent pattern of length of data or strings in between pieces of data, which can vary widely. An example of the data would be something like this: XXXXX YYYYY NN XXXXXXX * For testing purposes, I need to come up with a way to parse the data for easier viewing. The current approach is this: * Take the string and divide it up into individual cells by using the MID function. In the above approach, this would split the data into four (4) different cells. This is going to be a very complex workbook with data strings that can have 20+ elements in them, and have lots of separate records, etc. While this way should work (if everything is entered perfectly and no data structures ever change, etc.),
View Replies!
View Related
Mid Function Returns Odd Results
I'm using the following phrase out of a much longer expression to demonstrate that the return value of the MID function seems to be nothing I can understand. This expression: =MID("6/10x",1,FIND("x","6/10x)1)) returns 6/10 as text which makes sense. However I was under the impression that when text in Excel contains numeric characters, it can be interpreted as a number. So then how does the following expression, =MID("6/10x",1,FIND("x","6/10x)1)*1 return the result 38878? Does it still think it's text?
View Replies!
View Related
Using MID And Stoping When You Hit More Than A Single Space
I have a large spreadsheet with a data dump from a database program. The formatting of the exported data dropped multiple values into the same cell. I am trying to come up with an equation to separate these values into separate cells. The problem is that the length of the data varies. I don't need the first 6 characters of each line. I was thinking of using MID to start at the 7th character. The second piece of data that I want to capture on each line varies in length. There is, however, multiple spaces between the second piece of data and the third. Is there a way using MID to have the count stop once it encounters more than a single space?
View Replies!
View Related
MID Function Formula String Extract
Cell A1 contains 012345678 ** Customer of 987654321 ** Cell B2 contains 087654321 My formula =MID(A1,FIND("of",A1)+2,10) works perfect if it has a "customer of...". How to revise my formula that when it gets to cell B2 and if there is no "** Customer of ..." it would leave it blank or zero rather than #Value!?
View Replies!
View Related
MID Function :: Numeric, Alphanumeric Or Alpha
A2 cell data = 11 digits alphanumeric number. * begin with either "0" or "1" I want to the formula to look at the 6th and 6th digit (numeric, alphanumeric, or Alpha) and compare it with the cells under "'PSCCR Queue  Manager" TAB from C4 thru C7. Below formula is only looking at the first value when comparing. =IF(A2="","",IF(ISNUMBER(MATCH(MID(A2,6,2),'PSCCR Queue  Manager'!$C$4:$C$7,0)),"ITS","Non ITS"))
View Replies!
View Related
Mid, Left, Right Function Macro Error
I've got a little application in excel using macros. It worked perfectly with Excel 2000, but with Excel 2003 shows an error for every formula I've used in the macro right, left, mid, date... Sub buscar_libros_datos() On Error Resume Next Dim libro As Variant Dim ruta As Variant Dim libroruta As Variant Dim tabcol As Variant Dim tabfil As Variant Dim posguion As Variant Dim posparen1 As Variant Dim posparen2 As Variant Dim categoria As Variant Dim categant As Variant Dim tipo_info As Variant Dim fecha_carga As Variant Dim i As Variant
View Replies!
View Related
Random 3 Letter Prefix Form (left, Right, Mid)
i need to create a random 3 letter prefix form eg. British Head Office, how would you pick letters out and compare the 3 letter prefix back to a column with existing prefix. and if u can help me even more how can i automate this method beacuase i for eg. get like 50 company names at a time and dont have time to do and check them 1 by 1 lol.
View Replies!
View Related
Conditional Format For Low, Mid & High
Sorry I don't mean to bring up old post of mine but my boss wanted me to change how this works. I have placed a link to the old post of mine in hopes that it may help. Instead of having two conditions that turn green, he wanted to combine those two, have the lower turn red and the higher turn yellow. Still only three colors but I am unfamilar with how these conditional formats work.
View Replies!
View Related
Storing/Copying/Pasting MID Function Output
I am having some trouble with the "mid()" function. I have it saved as a variable which pulls 6 digits from another variable. I was trying to paste from the mid variable into my excel worksheet but it keeps on telling me that there is an object required error. The goal is to input the "mid()" output in my code into a cell in the spreadsheet. Here is some sample
View Replies!
View Related
Sort Rows By Mid() String Of Column Cells
I have a single column dynamic range called "Dock" and I need to sort the rows in my worksheet based on information from this range. Each cell in "Dock" is formated similarly to "TM09030010" This is "TM" & YYMM & "4 digit number" I need to sort my rows based first on Date "YYMM" then second by the last 4 digits of the same string. The worksheet has information in columns A through K and Range "Dock" is located within column A.
View Replies!
View Related
Mid Function :: Match Account Number With Description
I am trying to use a Mid function to help me in my vba problem. In my first table I have a list of account numbers. E.g. 'ZZ500543' In my second table I have a list of account descriptions. E.g. 'Denis Morgan ZZ500543 leak from bath' I need to match the account number in Table1 with the correct account description in Table2. I thought a Mid function would be the best option to pick out the account number in Table2? Is this right? How would I go about using it?
View Replies!
View Related
Move Mid Text In Cell To Front. Reverse
I am working with a column which always displays the cell contents with oldest notes by date first, then the newest note last. I would like to have the contents from a cell reversed with the newest user input by date on top which will simply when reading. Here is an example of a cell. The string always starts with (MM/DD/YYYY TT:TT:TT AM, then user name). 12/20/2007 8:27:56 AM MBARNEY Approval not required for this change request. 01/25/2008 10:27:32 AM KVELDANDA Change to Category, Type, Item, Region, Site, Department fields triggered an evaluation of whether or not the Change requires approval. Approval is not required.
View Replies!
View Related
Convert Mid & Find Function To Vba
I am writing VBA code that dissects a cell which always contains some alphanumeric characters a colon ":", some more characters, another colon ":" and some more characters. For Example this is what is in my cell "Deposit:93121:Southern California" what I need is to cut and paste everything that is located after the 2nd colon ":" Say that my cell is D433 and in nonvba world I can chop off the first part of the cell to the left of the first colon ":" by using the following formula: =MID(D433, FIND(":",D433)+1,50) assuming there are less than 50 characters in my cell, which is a safe number, then I cut and pastespecial this cell as values into the same cell and repeat that same formula and voila, what remains in cell D433 is what I am looking for "Southern California" how can I replicate this surgical process in VBA, is there a way to combine this process in one command on any given target cell?
View Replies!
View Related
Got A "Mid": Copy A Value To A Refer Contained Within Another Cell
I am trying to copy a value to a ref contained within another cell. BF3 is where the formula is to copy BJ1 contains the cell ref of "H6" BI10 is the next cell to select I think the problem is activecell = Mid(BJ1,1,5) which is the cell to which i want to paste the value. BJ1 contains the ref in text format of "H6" I am therefore trying to copy the value in BF3 to H6
View Replies!
View Related
SUMIFS Function In 2007 (range To Sum, Range To Evaluate, Evaluation Criterion)
I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula: {=SUMIFS(range to sum, range to evaluate, evaluation criterion)} The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999. So my formula looks like this: {=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)} This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?
View Replies!
View Related
Function To Sort A Range By The Values In A Specific Column And Return The Range
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my innermost loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003. My Public Function SortRange(rngToSort As Range, valCol As Integer) Dim Swapper As Variant Dim i As Integer, _ j As Integer, _ k As Integer For i = 1 To rngToSort.Rows.Count For j = 1 To rngToSort.Rows.Count  i If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then For k = 1 To rngToSort.Columns.Count Swapper = rngToSort(j, k) rngToSort(j, k) = rngToSort(j + 1, k) rngToSort(j + 1, k) = Swapper Next k End If Next j Next i SortRange = rngToSort End Function
View Replies!
View Related
Get The Range Address Of A Dynamically Named Range That Refers To A Formula In VBA
I have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange. I have another named range that actually refers to a range. Call it AllData. Column A Row2 56 Row3 44 Row4 65 AllData is a named range that refers to the range A2:A65536 AllData_UsedRange refers to A2:A4 by way of this formula. =OFFSET(AllData,0,0,COUNTA(AllData)) How to I obtain an address of AllData_UsedRange in VBA code? These do not work... ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address Evaluate(ThisWorkbook.Names("AllData_UsedRange"))
View Replies!
View Related
Find Dates Between Monthly Range And Sum Another Cells Results That Are In A Range
I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.
View Replies!
View Related
