VBA ISNUMBER, ISTEXT Functions

Jan 13, 2005

I am trying to create code that deletes lines in a spreadsheet when the first column is either blank or has text in it. However, when writing the VBA code, ISNUMBER and ISTEXT do not work. Amazingly, ISBLANK does work.

View 4 Replies


ADVERTISEMENT

Isnumber Or Istext Formula

Feb 21, 2010

I've been trying to use excel's Isnumber or Istext function to evaluate a range of cells, but have been unsuccesful so far. Is it even possible? or is the formula just restricted to evaluating only single cells?

For example, if I do, Isnumber(A1:A10), is there any way to make that work?

View 9 Replies View Related

Check Cell For Data. Tried ISREF, ISBLANK, ISNUMBER, ISTEXT.

Mar 24, 2009

I have 4 cols, x rows. I need Column C to check Column B for a numerical value, and if true, return the value in B, and if not true, then return value from A. Column B are functions formatted as general. The reference made by Sheet2!B is numbers formatted as text as to retain leading zeros. Here is what I have so far
=IF(ISBLANK($B10),$A10,$B10)

View 5 Replies View Related

Nesting Isnumber Search Functions

May 25, 2007

I have this search function that works well: =IF(ISNUMBER(SEARCH("which is < ",E9)),"test ok", "not ok") my E9 cell contains the phase: "I have a number which is < 9, and which is positive..." E9 has "which is < ", then i get back "test ok"

My question is.... how can I do this multiple times within the same function/cell. i.e. if I wanted to search "which is < " and/or "positive" in the same cell, then i get back "test ok", "positive number") I keep trying all combinations to no avail, like: =IF(ISNUMBER(SEARCH("which is < ",E9)),"test ok", "not ok"), =IF(ISNUMBER(SEARCH("positive",E9)),"positive number", "no good")

Keep in mind I am using a numeric example, although I am using this function to check if part of a cell matches specific text...

View 6 Replies View Related

ISTEXT In Array SUM

Jul 31, 2009

I have a workbook with worksheets named by peoples names. There are cells in my summary sheet that contain all the names of the worksheets (or peoples names).

Each worksheet has a table in it that I am trying to search one column to find a specific name and then report if there is text in another column in the same row. If I use the following formula:

=IF(ISTEXT(INDEX(INDIRECT("'" & E$4 & "'!f10"),MATCH(INDIRECT("A11"),INDIRECT("'" & E$4 & "'!h10"))))=TRUE,1,0)

It works and I'll return a "1" for cells in column F that have text and a "0" for columns that don't.

However, I am trying to sum up the cells in F that have text and when I enter:

{=SUM(IF(ISTEXT(INDEX(INDIRECT("'" & E$4 & "'!F10:F200"),MATCH(INDIRECT("A11"),INDIRECT("'" & E$4 & "'!H10:H200")))),1,0))}

it returns "1" when that should not be the answer. Am I doing something with the array in my INDIRECT formula? I used this similar application for another purpose in this workbook and refereced the array through INDIRECT the same way (but didn't use MATCH or INDEX) and it worked fine. This was the formula:

{=IF(SUM(IF(INDIRECT("'" & C$4 & "'!H1:H200")=INDIRECT("A5"),1,0))>0,SUM(IF(INDIRECT("'" & C$4 & "'!H1:H200")=INDIRECT("A5"),1,0)),"-")}

View 9 Replies View Related

IF (ISTEXT Formula)

Jan 28, 2010

I am summing 3 cells, A3, A4 & A5. Cells A4 and A5 may contain text. I want to write a formula using the IF(ISTEXT funciton which says something like this:

Sum(A3:A5), if cell A4 and A5 contain text then sum just A3. If only cell A5 contains text then sum just A3:A4.

Im not sure exaclty how to construct this.

View 9 Replies View Related

=IF(ISTEXT On Blank With Formula?

Mar 15, 2009

I have this formula in a cell: =IF(ISTEXT(F5),F4,""). The problem is F5 is visually blank and has this formula =B3. so it returns F4. Is there a way to rewrite the formula or to use another formula, so it will return blank?

View 2 Replies View Related

Searching A Cell With IF And ISTEXT

Mar 20, 2014

I'd like to search a cell A1 containing for example 'hello_L765'. If the 7th character is L and the 8th character is text(ie not a number) than display yes, otherwise display the 7th character.

View 3 Replies View Related

Can ISTEXT Search A Range

Jul 5, 2007

I'm currently designing a new spreadsheet and I can't change the formula I'm using below to search more than one cell or a range. Is either choices a option?

I'm using =IF(ISTEXT(E135),G135) Can I add cell E139 (and how many additional cells could be added) or can I set it up to search E135:E144.

View 13 Replies View Related

Multiple IF And ISTEXT Within Same Formula

Jun 23, 2014

I have a rota sheet whereby shifts are written in as start time, finish time, and then a calculation for the hours.

At the bottom I have another SUM formula to calculate total daily hours.

However, within the total personal hours I have a VLOOKUP formula to check if the start time cell has a holiday text reference.

Please see below:

Take the "Sergei" example: he starts at 9am, finishes at 8pm, and the formula automatically calculates an 11 hour working day.

However, in the "Tom" example, the "TH" refers to another sheet which houses a reference of the amount of hours to place in the total hours box if those letters appear, thus imputing holiday hours at the same time.

"=IF(ISTEXT(F24),VLOOKUP(F24,'Hol-Meet Hours'!$F$7:$G$27,2,0),(G24-F24)*24-IF((G24-F24)*24>6,0,0))"

What I am struggling with is to use an IF and ISTEXT formula in the "Daily Hours" formula to ignore any holiday inputs, thus only giving me trading hours.

The only way I could think of doing it was something along these lines:

"IF(ISTEXT(F16),SUM(F17:F31),SUM(F16:F31)...et al"

The issue here is that I need to repeat the formula for every staff member within the same cell's formula, which I don't know how to do (multiple functions within a formula repeated 20 times), and is a right drag.

View 5 Replies View Related

ISTEXT Returning False

Nov 12, 2009

I'm trying to apply the istext to a cell that has multiple values eg: [car, boat, truck] so I have setup the cell with the formula

=istext(c147 = "car",1,0)

it returns false constantly. Is that becuase of the other values separated by comma's in that same cell?

Is there a way to do this so that I don't have to go through 300+ cells that have multiple values to separate them?

View 9 Replies View Related

Excel 2007 :: Validation With ISTEXT Not Working When Number In Cell?

Dec 3, 2012

I'm trying to validate a cell in Excel 2007 that should contain only two letters.

Formula:
Code:
=IF(AND(LEN(A1)=2,ISTEXT(A1)),TRUE,FALSE)
When the value of A1 is JK, the result is TRUE
When the value of A1 is 12, the result is FALSE

When the value of A1 is 3K, the result is TRUE
When the value of A1 is K3, the result is TRUE

When the value of A1 is 123, the result is FALSE
When the value of A1 is JKL, the result is FALSE

When the value of A1 is 3, the result is FALSE
When the value of A1 is K, the result is FALSE

The LEN function works as expected, but the ISTEXT function does not, whenever the cell contains a number and a letter. I've tested the LEN and ISTEXT functions separately, and get the same results.

Is it me, or is it Excel?

View 7 Replies View Related

ISNUMBER

Nov 12, 2008

Im using ISNUMBER in a spreadsheet to automatically add a ref number to an entry in the spreadsheet, this is based on the value of a set column. To give you an idea the values are bus services, i.g. 6, 123, 56 etc, when the column has a bus service number entered it auto produces a ref number.

Now the problem is now some service numbers have letters in e.g. X14 X12 36A etc, now these are not recognised and therefore no ref number is given, is there a way of changing my formula to recognise this.

The formula at the moment is as follows

In the ref column (which is locked) =IF(V4=1,COUNTIF($V$3:V4,V4),"")

In column V (which is locked and hiden) =IF(ISNUMBER(D4),1,"""")

View 9 Replies View Related

IF(ISNUMBER)

Jul 24, 2009

The following formula:

=IF(ISNUMBER(F35)="TRUE","",IF(ISNUMBER(E35)="TRUE","X",IF(ISNUMBER(D35)="TRUE","XX","XXX")))

is returning XXX when copied down, where I expected it to return XXX as well as XX, X and " ".
Has anyone got a clue what I'm missing (might be a friday afternoon thing...).

View 9 Replies View Related

OR With ISNUMBER

Apr 21, 2006

I am faced with the daunting assignment of adjusting a payroll report using infrequently exercised logical expressions. I've been careful to try and build my formula adjustments in intermediate steps, so I could test the results, but I can't seem to get past a dual test. I need to create a commission calculation if there are no values in the two columns immediately left of my active cell,col N79, i.e., if L79 OR M79 contain a value, then do nothing, otherwise, multiply contents of G79 * the commission rate contained in named cell "Split3".

This formula works on a single column test:
=IF(ISNUMBER(M79)," ",G79*Split3)

But I can't seem to construct a formula to test both column cells. I have tried this unsuccessfully:
=IF(OR(ISNUMBER(L79),ISNUMBER(M79)," "),G79*Split3)

The above formula is accepted, but I get: #VALUE, with the explanation:
"A value used in the formula is the wrong data type"

View 2 Replies View Related

Can I Use Sumproduct And ISNUMBER Together

Sep 27, 2007

I need to create a formula (Sumproduct) that will look at two criteria then total a third column IF the value in the cell of the third column is a number.

The sumproduct will look at one range A1:A100 and see if it matches initials in cell D1, and it will look at the year in column B1:B100 and see if it matches the year in D2. Then it should total the amount in column C1:C100. But the column C to be totaled includes both dollar values and “N/A”. So If I use something like:

=SUMPRODUCT((A1:A100=D1)*(B1:B100=D2)*(C1:C27))

I get the #NAME? I believe because some of the values are N/A instead of numbers. So can I say something like:

=SUMPRODUCT((A1:A100=D1)*(B1:B100=HD2)*(ISNUMBER(C1:C100)*(C1:C2100)))

(I know this probably isn’t even close, but I hope you can see what I want to do)

I know I can create a separate column to show only the dollar values from column C and use the sumproduct to total that column, but is there a why to skip that step?

View 9 Replies View Related

Isnumber Query

Nov 28, 2007

Can someone tell me how to have 2 options using isnumber.

I'm using ISNUMBER(SEARCH("SUBMITTED",N156)) but I'd like to have "SUPPLY" & "SUBMITTED"

View 9 Replies View Related

Vlookup And Isnumber

Oct 17, 2008

Here is some of my code. It does not seem to work though. can you fix it?

Dim Madrid As Integer
Dim Paris As Integer

For n = 7 to 17

Madrid = Application.WorksheetFunction.IsNumber(Sheets(3).Value("P" & n + 1).Value)

Paris = Application.WorksheetFunction.VLookup(Sheets("Sales").Value("$E:$I"), 3, False)

View 9 Replies View Related

ISNUMBER Function ..

Jan 8, 2009

I have this formula, I have 2 cells that i eneter times in, I would like to have the formula do this..

I would like to have it only display if there is a value in the cell. If value is in both cells then it would display both cells

=TEXT(Inbound!AP25,"h:mm AM/PM") &" / "&TEXT(Inbound!AP40,"h:mm AM/PM")

View 9 Replies View Related

IsNumber Is Not Working

May 28, 2006

Can anyone identify the error in my looping? My IsNumber(f) is not working. If I enter a string, an error message will pop up, but if I enter a number (e.g 4 or -4), the same error message pops up too...what can I do?

I cant really set the inputbox to Type:=1, because I want this inputbox to be optional to the user, i.e If user does not enter anything, it will return a 2.

Sub frequency2()

'Get the frequency of coupon payments per year

frequency = Application.InputBox("Please enter the frequency of the coupon payments", _
"Frequency of the coupon payments", , , , , 1)

'If c = 0 Then 'If it is a zero-coupon bond, set the frequency of coupon payments to 0
'frequency1 = 0
'Debug.Print frequency1
'ElseIf frequency = False Then
'frequency1 = 0
'Else
frequency1 = determinefrequency(frequency)
Debug.Print frequency1
'End If
End Sub

' This function automatically sets the value 2 to the Frequency of Coupon Payment of the Bond if the user
' did not enter anything when prompted.
' It also validates if the user entered negative numbers
Function determinefrequency(Optional ByVal f As Variant) As Variant

Dim testt As Boolean

Do
' If values have been entered into the inputbox and
' the userinput is a number
If Len(f) > 0 And WorksheetFunction.IsNumber(f) Then

View 3 Replies View Related

ISNUMBER(SEARCH Function)

Jul 14, 2009

I have recorded the below code, is it possible to adjust this so that instead of using "USD" the macro will reference whatever currency the user inputs into say sheet1 cell A1?

View 2 Replies View Related

Using IsNumber Formula For Range?

Dec 20, 2011

formula to check a range for any text entires by error.

Example: I have a range of numbers on column A1:A1000, at the end of this range on A1001, I have been trying to use the following formula...

=isNumber(A1:A1000)

if like on A7 it had "525a" instead of "525".

View 4 Replies View Related

ISNUMBER Giving Me Two Different Results

Jan 24, 2007

I have a problem with isnumber giving me two different results. One time it will evaluate the following as true:

ISNUMBER(0)

and in the same sheet it will evaluate the same as false.

I need ISNUMBER to NOT recognise "0" as a number all the time.

View 9 Replies View Related

CountIfs And Search (ISNumber) Function Together

Aug 11, 2014

I'm trying to develop a formula that can incorporate the search function in amongst a countifs formula. I have a column that contans the string "2.3 Manage Project Delivery" in a single cell. However, a single cell could also contain this text string in amongst other text and be in there multiple times - E.G; "2.1 Manage Customer Support, 2.3 Manage Project Delivery, 2.4 Close Program, 2.3 Manage Project Delivery" etc

My formula currently looks like this: =COUNTIFS('RDC Register'!$AW:$AW,"Not Overdue",'RDC Register'!$C:$C,"2.3 Manage Project Delivery") but it's not counting the cells that have 2.3 Manage Project Delivery in it more than once.

So basically I need to modify my formula to search for this text string in the cell and add all occurrences to the final count.

View 3 Replies View Related

Conditionally Format IF, ISNUMBER, MATCH

Jan 14, 2009

the attached document. I wish to try and conditionally format Sheet1!D:D depending on if Sheet2!E:E displays a particular letter, when; Matching Sheet1!A:A to Sheet2!A:A Matching Sheet1!B:B if <11 to Sheet2!B:B where cell equals text "Small (7-10)" if Sheet1!B:B is >10 then where Sheet2!B:B equals text "Mid - Large (11-16)"
Matching Sheet1!C:C if cell contains "Hcap" to Sheet2!C:C where cell equals "Handicaps". If Sheet1!C:C doesnt contain "Hcap" match to Sheet2!C:C where cell equals "All Races".

To use an example, if E:E in the cell adjacent to all the matches above equals N then the cell in Sheet1!D:D turns red. Cell D52 would become red. D88, D91, D92, D93 and D96 would also become red. Here was my attempt at the formula, no idea if I was close to the answer.

View 2 Replies View Related

Average If Isnumber Multiple Values

Jul 23, 2008

If A1= 5 ; C1= #N/A ,F5= 7 and E7= 8

Any formula which can average if isnumber A1,C1,F5,E7 ?

it shall ignore errors in this case.

View 9 Replies View Related

Nesting Isnumber Search Formulas

Jun 8, 2007

I am using the ISNUMBER formula to verify if specific text is found in a string of text.

This formula worked when I only needed to find 1 particular brand name:

=IF(ISNUMBER(SEARCH(""BrandB"",RC[6])),""B"",""C"")

Now I need to have 2 different brand names ("BrandB" and "J & B") that need to return a "B" and all others should return a "C".

I found a thread regarding this same topic, but I could not get their solution to work for me.
They recommended using:

=IF(ISNUMBER(SEARCH("BrandB",RC[6])),"B","C")&", "&IF(ISNUMBER(SEARCH("J & B",RC[6])),"B","C")

The fact that my second brand name is written with spaces ("J & B") could be adding to the problem, I don't know. I thought replacing the "C" with another "IF(ISNUMBER" function should work, but I have been unsuccessful at writing a formula that doesn't cause run-time errors.

View 9 Replies View Related

IFERROR Or ISNUMBER - Checking Whether Or Not Two Different Cells Contain A Number

Jan 26, 2014

I am checking whether or not two different cells contain a number or the error #NA.

If one of the cells contains a number then the result will be that number.

If both cells contain #NA then the result should be “99â€

Example:
Formula in Column C
The values are in columns A and B.
If the value in cell A is a number the result is A.
If the value in cell B is a number the result is B.
If the value in cell A and B equal #NA the result is 99

View 6 Replies View Related

IF(ISNumber): Returning A Value For The Alpha-numberics That Begin With DMR

Jan 28, 2010

See attached file...I'm having trouble returning a value for the Alpha-numberics that begin with DMR, which should return 'Dishwasher'. All other values are correct, I'm sure it has to do with the character range but I'm a little stumped.

View 4 Replies View Related

"istext" Behaviour/recognition

Apr 4, 2009

was workin' on a text recognition question with someone here:
http://www.excelforum.com/excel-new-...ml#post2071064

and another user posted this:

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved