Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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)/60-MID(A1:A5;1;2)+0-MID(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 Mid-macro
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, pre-populate 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 alpha-numeric 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)-2-FIND(":",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
Use Mid To Extract String
I need to extract the term between the two colons: Snowboard Jackets

This term resides in AB289-
Snow : Snowboard Jackets : Guys SoftShell

Using MID, but not able to get all the term


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 03-Parts & Service Systems-GSMS-Test " 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
Extracting Mid Strings From A Cell
i have in a cell a Text like this..

Chicago (TZGUG)

i tried a formula.. like this..

=LEFT(F3;LEN(F3)-1

to remove only ) ..

but how can i remove the ( too ..

and also Chicago

so the result in a cell would be TZGUG

View Replies!   View Related
Calculate STDEV For Mid-95% Of Data
I have a worksheet containing data in columns. For each column, I would like to calculate the standard deviation of the mid-95% 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
Mid Find First Capital Letter
I require a formula which finds the he first capital letter within a cell and returns the text, but I need it to ignore blank cells and the symbol *.

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 1-32, 33-64, 65-95

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
Function Similar To LEFT / MID / RIGHT
I need to return the text from a string, in a similar way to they way LEFT/ RIGHT would do.

I have some text in a cell, say A1 that looks like this ->

TEXT BEGIN --- TEXT NOW ENDS

I want to return everything before / after the --- symbols.

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
Inserting Text In Mid String
Is there any function or methodology to insert text in mid string? ex. I would like to insert a string after the dashes below.

0506-40365503970
0506-40365752746
0506-40365994154

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
Left And Mid Functions Inside Subtotal
Have been messing around a bit with this.

So this is the formula without LEFT
=SUBTOTAL(9,C17:C22)

i want this
=SUBTOTAL(9,LEFT(C17:C22,1))

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 alpha-numeric number.

* begin with either "0" or "1"

I want to the formula to look at the 6th and 6th digit (numeric, alpha-numeric, 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
Use FIND And MID Function For Extract From String
I know I have to use FIND and MID but I can't work out the right syntax: how do I extract Michael or Dave from the following strings:

F - Michael 8735
M - Dave 093

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
MID FIND LEN (return The Matching Word In V6)
=MID(M11,FIND($V$6,M11),LEN($V$6))
I use the formula above to return the the matching word in v6, from text within m11. If theres is no match it returns #value. is it possible when theres no match to return "Not Match" in the cell instead

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
Substitute Function With Find, Mid & Left
Got the following formula:-

=LEFT(L5, FIND("(",L5)-1)

I need to add the SUBSTITUTE function to this but can't figure out where it goes if somebody could point me in the right direction please? My substitute formula is SUBSTITUTE(L5,"car","train").

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 non-vba 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
MID Formula: Extract Only The Text Between The 1st And 2nd Dash In A Cell
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

View Replies!   View Related
Functions Left, Right, Mid And Trim: Early Binding And Late Binding
I have some code that run on Excel 2003, and fail on Excel 2000. It happens on functions Left, Right, Mid and Trim. I've found that I must use in "Late Bindings".
If exist any convertion for above functions?

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)
Im 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 inner-most 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
Dynamic Named Range Sort Including Cells Outside Defined Range
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....

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
Application.inputbox: Range To Copy And Paste The Range's Link And Format To A Different Sheet
Need a code using application.inputbox to get a range, then use that range to copy and paste the range's link and format to a different sheet? The specifics don't matter, I just can't figure out the syntax. Here is what I have currently:

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved