I'm trying to use an IF function to evaluate a cell containing a text string. The string is two words with a space; "Turned ON" or "Turned OFF". The formula I'm using is "IF(D15=("Turned ON"),1,0)". It always returns 0 as the result. If I eliminate the space it works fine, but I would have thousands of entries to correct. Any thoughts?
View 8 RepliesI am using the following code.
Function VLOOKUPRow()
Dim SV As Object
Worksheets("Midterm").Select
SV = "UserFormAppend.TextBoxInvID.Text"
VRow = Columns(1). Find(What:="SV", After:= Cells(1, 1), _
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=True).Row
MsgBox (VRow)
End Function
I am trying to give the VRow variable the value or the row that The text from my text box is on. How do you make the what look for a variable. I can get it to work if i put a constent in the what. how to make the what look for a variable. I'm new at this forum thing so im not quite sure how to check for answers.
I can't get the text box function to work on this spreadsheet.
View 2 Replies View RelatedI have a spredsheet with multiple Alpha Numeric codes in one cell. I would like to seperate the codes but instead of placing them in the adjacent columns, like the text to columns function does, I want them to go to the preceding rows.
View 3 Replies View RelatedIn column A i will be entering Jobs in the format : 'Customer1 Job4', 'Customer 5 Job 4'. In column B i would liek to identify the jobs automatically, so a formula that goes something like : IF(colum A contains "Job 2", "Job 2",If (Column A contains "Job 3","Job 3","")). There are only about 6 jobs but there could be many customers.
View 4 Replies View RelatedThe following is my existing formula (I admit it is probably very far from what it should be) but I'm looking a way to base the conditional part of a function based on the text within a cell. =IF(E4=OR("WordA","WordB"),(I4),(-1*I4))
There are four words that I use to classify the column "E" for sake of simplicity I will name them as "WordA" "WordB" "WordC" and "WordD". Those words serve as the identifier that I am trying to test. I.E. if "WordA" or "WordB" are present in column E, I want the end result (listed in column I) to be a positive number. If "WordC" or "WordD" are present in column E, I want the end result listed in column I to be a negative number. Currently I have a formula in column I, which is: =((H3-G3)/G3).
Both Column H and G have numerical values. Essentially the whole goal of my process is that if WordA or WordB show up in E, then I want my formula in I to remain "as-is". If however, WordC or WordD are present, I want the end result or formula in column I to be negative.
I am not tied down or restricted to using a particular formula, but just don't know how to set it up either way. Additionally, I plan to copy the formula down in terms of numbers... i.e. I5,I6,I7, etc. so when submitting any advice or if supplying a formula to paste.
I have a list of items with a cell, however these are on seperate lines (using Alt+Ent) function. Example of entered text within the cell below:
_UN
_OD
_PN
_H
The beginning of each line will always start with an underscore ( _ ). The items within the list will either be 2 or 3 characters long (which includes the underscore).
The required output I'd like is (spaces used to indicate seperate cells):
_UN _OD _PN _H
I'm trying to use the 'Text to Column' function to solve my problem, however I haven't yet managed to get it to work. I've tried using the 'Fixed Width' function within this, however when I use this, it inserts an 'Enter' within the cell, which I don't want.
Does anyone else have a solution? Any help would be appreciated. Preferably I'd like this to be automatic using a formula, instead of me having to click the 'Text to Column' button each time.
(I'm using Excel 2007 if this makes any difference too)
This formula seems to work fine for the first two logical tests, but when it come to adding the third, I get an error.
=IF(E5="Hourly",C5*D5,IF(E5="Daily",(C5*D5)*8,IF(E5="Weekly",(C5*D5)*32,''"))
In column A there is a date and time function in the format as follows :
dd/mm/yy hh:mm:ss most of the time.
When this is downloaded it is easy to manuplite. But some times it show as
above but as a text string or sometimes the dd/mm is switched to mm/dd.
Does anyone know the work rounds these formulas?
I use the dd/mm and Hr function to complie reports from column A but usually
spend hours using mid() left() function due to the date change.
I have tried formating the cell but this does not work?
I think there's a function that compares two text values and determines if
they are "similar." For instance ABC would be considered a match with ZABC.
Is anyone familiar with this function?
In a single cell, I want to say "Report Complete as of" and then the current date. Is there a way to use & the TODAY function that will return TODAY as a date in conjunction with the text? I keep getting "Report Complete as of 39743" instead of "Report Complete as of 10/22/08", for example, and I can't seem to convert the numbers to a date.
View 2 Replies View RelatedI need to find a way to take the TODAY() function and and split the individual digits out and recombine them to a text cell, i have tried using the MID function but it returns the serial rather than the actual number, e.g
today() 02/01/2009 needs to be shown as 020108 (this needs to be text rather than a number) as it is included within an INDIRECT formula.
I am trying to use the "MAX" function to find the max temp on a particular day in a week from 7 separate sheets. The problem I have is one particular company autopopulates their temperature block with the temp and degree symbol: 46°F instead of just 46. The max function is thrown off it appears by the symbol and letter. Is there a way to get the max function to only look at the numbers? I'm also open to the option of autoconverting each temp to a number on the master sheet and letting the "MAX" function search through those if that's possible.
View 10 Replies View RelatedI have a cell with a text string >3, and would like it to return the number (not text) 3. I could use Right but since the number will not only be single digit I don't think this will work.
View 9 Replies View RelatedI've been looking for a while on here now and have found some great formulas, but can't seem to get this 100% figured out. Here is what I want to do. My master sheet contains a cell (A5) that is used as a keyword search. So if I wanted to find info using my name I would enter gsheppar in A5. I have another sheet labeled "List" where there are different categories i.e. A1 is labeled Online Retailers, A2:A51 contains a list of the retailers. In B2:B51 I would enter my name (gsheppar) if I had an association with the retailer.
On the master sheet when I enter my name I would like it to show all retailers in B5:B55 that has my name next to it in B2:B51 from the "List" sheet. The problem I am running into for example B2 on the list sheet has my name plus 3 other people's names formatted: gsheppar,name 2, name 3. The question I have is how to have the formula in B2:B51 on the master sheet look specifically for the name that is in the keyword search when there are multiple names in a single cell on the "list" sheet.
I have a multipage control on a userform. On Page 8 of that multipage, I have label1, part of that label is modified by other parts of the program. This is done by the program writing to a sheet of Data and then the program pulls that info into this label. My question is,what is the correct format to populate the label?
see below: This is what I have in my Userform_Activate and Userform_Initialize subs:
CurrentVersion = Sheets("Data"). Range("N2").Value
Label1.Caption = "This will allow you to change the Caption of the UserForms through out the operational program. Some UserForms cannot be changed. The ending of" + Chr(32) + Chr(34) + "Automatic 2005 V" + Chr(32) + CurrentVersion + Chr(34) + Chr(32) + " will be added to what you enter below as default."
CurrentVersion is pulled off of the datasheet which is a number like 5.9.8.2. however it errors "Run time error 13 - type mismatch" I can only suspect the label1.Caption needs something like: Multipage8.label1.caption =
I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it. For example "DFL0715HRS." I have been able to get the following formula to work:
=CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")
However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI." For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work.
It shows "DFL07157R0.": =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))
I think it is trying to continue doing something with the text function
I have a problem when using the function of text(). I set "=TEXT(SUM(A1:A5), 0) + 5" in Cell A6. Assume the answer of SUM(A1:A5) is 10, the result in A6 will be 15.
My question is how to prohibit a string value [Text(SUM(A1:A5),0)] add a number value [5] to form the result 15.....
I have 3 columns of numerical data. In my fourth column, I would like to return the minimum value from the 3 previous columns (=min). However, instead of returning the lowest number, I would like it to return the column title (=text) of the lowest number. I haven't been able to locate a function that does this.
View 4 Replies View Related I am wondering if the user can use 2 boxes to search and populate the record.
Ie./ Box 1= Vehicle Registration Box 2= Date
This sheet will be used throughout each month and each vehicle registration may have more than one record that month
So I need the user to be able to input the vehicle reg and for the form to populate the relevant record.
I current have a Unique reference which is a combo box that once you select it auto populate the record but this will take forever having to populate ach record to find which one the user is looking for.
I am trying to write a small macro involving Len and Left formulas. This will take input from two cells and and give the value on a third cell. I know how to do this on excel. This goes as follows:
Value on A5 = HB_AM_1
Value on B5 = 1
My formula to extract the specific text =LEFT(A5,LEN(A5)-LEN(B5)-1).
My result on D5= HB_AM
Here, my inputs are clearly defined and I can write this formula on cell D5 to get the result. I can copy this down to the end of my data and I can get my result.
My challenges: I need to do this for various reports where the data extends to various rows. I think looping will solve the problem, but not sure how to do it. The value on cell B5 will always show the last number at the cell A5 (1 in the eg above). So, if the number in A5 is 10, value in B5 will also be 10. This is already there in my data, so I do not worry about this part.
I'm looking to copy 1000 cells of singular form text into the next column
and make it plural. Is there a function to do that?
I'm trying to write a statement something like: B1 = data. B2=IF(B1/8=0,"",IF(B1/8=INTEGER,B1/8 & "8/8",B1/8+1)). This is meant to identify which relay on a relay board is associated with the given data. For example if data=3, B2 = 1 3/8 meaning the 3rd relay on board 1. If data = 77, B2 = 10 5/8 meaning the 5th relay on board 10. I could do without the 10, but the 5th relay on that board is important to me. In summary, I think my trouble is identifying when the product of B1/8 is an integer. I may also be having trouble combining value and text. Well, there it is. I may very well be going about it all wrong.
View 3 Replies View Relatedway to use a function to remove text from a cell. I cannot use VB code, due to the spreadsheet being integrated in an operational software application - already tried it and get nothing but errors from the application. Was also trying to use a Find and replace function, but it's not working.
Here's the scenario - I have a cell that returns the weight of a product from the system, but it includes "LB" or "KG" in the cell along with the weight. I do not know how many characters will be returned to the cell, as it depends on the product. It could be a single digit up to 10 digits, including a decimal point. I need to clean up the cell to only a number, because in the next function, I am trying to round up the number that is left in that cell (won't round up with KG or LB in the way).
When I need to return numerical data with multiple criteria, I use the SUMIFS and it works fine. But when I need to return a text string, the function is not called for. What function can I use to make this happen? I've tried SUMPRODUCT, which I've had success with in the past, but it's not working this time. Maybe I'm laying it out wrong, but this is how I currently have it:
=SUMPRODUCT(column of text to be returned,((column of criteria1)=criteria1)*((column of criteria2)=criteria2))
Do I need to fix this formula or use something else?
Macro for text to columns function (if this is the best way to go).
Our work report exports customer subscriptions which we call "role name". Our customers subscriptions have a valid and start date, but our customers also can purchase multiple subscriptions and we need to separate this data in order to report by subscription.
Our export has an * after each subscription, the date is in square brackets and each subscription is separated by a semi column.
So for arguments sake, let's say we have the following subscriptions:
subscription a
subscription B
subscription C
subscription D
if one customer has purchased four subscriptions on various dates, their exports may look like this:
Subscription A* [01/02/2012 12:00:00 AM]* [01/02/2013 12:00:00AM]; Subscription B* [01/03/2012 12:00:00 AM]* [01/03/2013 12:00:00AM]; Subscription C* [01/04/2012 12:00:00 AM]* [01/04/2013 12:00:00AM]; Subscription D* [01/05/2012 12:00:00 AM]* [01/05/2013 12:00:00AM]
I then use a text to columns function to separate by the * and the;
I then am left with a column for the role, one for the start date and one for the expiry date.
The roles are in alphabetical order, but sometimes some manual sorting needs to be done to delete the roles I don’t need and keep the ones I do. For example someone could have subscribed to subscription B and not in A that means that the first column may not have all the roles I need, it may be in the second or third column depending on what other subscriptions they have.
So not sure if text to column is even the best way to go?
I have a spreadsheet with columns of data running from column C to column Z.
In column B i am trying to work out which word appears the most.
For exaple in cell B2 i am looking for a formula to work out which word appears the most from cells C2:Z2.
I entered a pretty easy formula to pull the month from a date:
=MONTH(C23)
, where C23 = 12/11/2013. This returns the expected output of 12.
However, when I convert it to text:
=TEXT(MONTH(C23),"mmm"))
It outputs January instead of December. I am so confused, why would Excel 2010 return bogus output in this regard? I mean, I can use the =MONTH formula.
I want to use TextToColumn function of excel using vba. I tried recording a macro to get the syntax and then make a few changes to it; But it is not working when i run it from vba ... if i do it manually it is working very nicely but it just skips that step in vba.
i have pasted my code
Code:
On Error Resume Next
rngReqIDcodWhole.TextToColumns Destination:=rngReqIDcodWhole, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:="""", FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
rngReqIDcodWhole is a column range
the text that the code needs to break is as follows
Code:
_BRE.RFs.DocPackage.Decpages.CreateContent_DECPAGES_RF(dre, sec, "FormID","FormID", 1, "DecR_1", "DecR_1", false);
I am trying to rank a list of numbers, such as:
1
3
5
3
4
1
I have no problem with the rank function in terms of the ties showing as duplicate values, however, when this occurs I would like a "T-" to appear before ranks that are tied, and show nothing if they are not tied. Essentially, I want the final result to look like this, without having to manually add the "T-" after the ranking is complete.
1 T-5 3 T-3 5 1 3 T-3 4 2 1 T-5
I have done more complex Excel formulas before, but for some reason this is stumping me.