If Integer Function And Combine Value And Text
Oct 12, 2008
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
ADVERTISEMENT
Oct 9, 2009
with a formula that would find a text data point (comma) and round the number to 2 decimal places that is located prior to the text data point (comma) within a string of data. see examples below in Column A and the intended results in Column B.
Column A
row 1: Joe 1.234, Al 6.89656,
row 2: Sam 6.5,
Column B
row 1: Joe 1.23, Al 6.90
row 2: Sam 6.50
View 9 Replies
View Related
Oct 4, 2012
At a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..
Sheet 1
Row 1: Mike │ "Great to work with"
Row 2: Mike │ "Bad manager"
Row 3: Tom │ "Great guy"
Row 4: Mike │ (blank)
Row 5: John │ "Cool"
Row 6: Mike │ "Best boss"
On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.
Sheet 2
="Comments for "&[ref to cell containing "Mike" in another sheet]
- Great to work with
- Bad manager
- Best boss
View 6 Replies
View Related
May 8, 2007
i've tried searching for "Mode Function in Text" , "Mode Function by Text" but to no avail. Can someone please guide me in how to do a Mode by Text? =MODE(number1,number2,...) But if i have a whole chunk of data in Strings in cells Range A1:C50
1) How can i get the mode of that Range ?
2) is it possible to get the statistic of those for e.g.
Apple 20
Pear 30
Watermelon 50
etc......
View 4 Replies
View Related
Oct 2, 2006
i did some VBA quite a while back, and picking it up again.
I'm trying to get a function to read in a string, and return a value based on the string value.
Public Function value(ItemType As String) As Integer
If ItemType = " Upper" Then
value = 40
ElseIf ItemType = "Middle" Then
value = 50
ElseIf ItemType = "Lower" Then
value = 25
End If
End Function
When i tried using it at the Cell level, it keep giving error i.e. Invalid Name Error
View 9 Replies
View Related
Apr 13, 2007
I'm on a time crunch and can't remember or seem to be able to find how to check if a value stored in a variable is a number or is text.
I want to be able to define QTY under the general public definition with no designation (i.e. integer, date, string...so on.)
The variable QTY could be anything and I grab some parsed data from a cell, if it is filled with a number I want to keep it, if not I want to delete it.
I know how to keep it or delete it, I just need to know how to check for integers...(check to see if it is a number from the keypad...I can still store it as text -- maybe integer isn't the best word).
I see you (whomever you is) fixed the "Deletked" in the message box...it is now Deleted. I can't give you a hard time about not following the rules now.
View 9 Replies
View Related
Apr 14, 2014
Please refer to attached file.
I need a VB Code to do the following. Column E,I,M,Q (every 4th column until column DU), have integer number as shown and search until "REPORT END".
I need to change the integer number to following alphabet.
9 = A
18 = B
20 = C
82 = D
83 = E
84 = F.....and so on until 93 =
Book1.xlsx‎
View 6 Replies
View Related
Feb 16, 2014
I've got an odd program that exports time data formatted as a text string. For example....
0:10:25
Since it has no numerical value, then I can't simply "format as number". I'm thinking that I will need to use one of the string methods eg: mid() to check each character individually and then convert that into the equivalent numerical value.
So the numerical output I am looking for in the above example would be 625.
View 2 Replies
View Related
Mar 11, 2007
I have a user form and to input the hourly wage i need to convert the text entry to a number whose type is LONG and not integer.
Does anyone know how to do this?
Or is there a way to input a number in a userform without a text box entry?
View 9 Replies
View Related
Jun 24, 2013
I have the following function:
=IF(AND(I17>=$I$1,O17>=$O$1,Q17>=$Q$1,F17
View 2 Replies
View Related
Jun 15, 2013
I am trying to combine the text values of several formulas in addition to adding some text (punctuation) in between.
Here is the data that I am trying to combine
C14: 2013
C15: Period 6
C16: Week 1
In Cell C13, I would like to have this value returned: "2013 - Period 6, Week 1"
Is there such a way to do this?
View 9 Replies
View Related
Feb 21, 2008
- I have excel file with data I need
- I have fixed txt(html) template that i need to integrate Excel information into
- Final result that I want to achieve is saved .txt(html) file with combination of fixed information (text) and data from excel cells.
I need to writing a VBA code for each of above (integrating text & cells, saving results as text)
View 5 Replies
View Related
Jan 23, 2014
Formula 1 : =IF(SEARCH({"spinning"},A2),"AUTOGEN")
Formula 2 : =IF(SEARCH({"typing"},A2),"Manual")
I want to combine above two formula's with "OR" function.
View 3 Replies
View Related
Oct 8, 2009
formula that would find a text data point (comma) and round the number to 2 decimal places that is located prior to the text data point (comma) within a string of data.
see examples below in Column A and the intended results in Column B.
Column A
Joe 1.234, Al 6.89656,
Sam 6.5,
Column B
Joe 1.23, Al 6.90
Sam 6.50
View 9 Replies
View Related
Sep 23, 2007
I have a date of the "Sep-07"in a1 in a2 i have "fred" and in a3 "expenses" when i try and combine them so that i can save the file name as a variable ie sept-07 fred expenses. I get
39336fredexpenses
How do I get the date not to read numbers?
View 5 Replies
View Related
Feb 25, 2014
I want to combine two cells and add text. Then delete column D.
View 4 Replies
View Related
Jun 16, 2008
I want the user to select a currency pair in cell B4, eg, EUR/USD
Then cell R4 will look to see if there is any data in B4 and, if so, return the following value into Cell R4....
=MT4|BID! & cell B4 & "m"
the cell b4 is the bit i am not sure about.
View 9 Replies
View Related
Nov 30, 2006
am trying to code a loop but I have no idea where to start.
With the help of you guys I have already got some code from a previous thread which gives me a list of hierarchical data now I want to be able to loop through this and actually have the 'parent' next to it.
For example:
1 Shops
2 Butchers
3 Meat
2 Bakery
3 Bread
I would like to be able to say
1 Shops
2 Butchers Shops
3 Meat Butchers
2 Bakery Shops
3 Bread Bakery
View 9 Replies
View Related
Aug 19, 2009
I have a column say column A that holds text. If the values match say:
ABC1 or
ABC2 or
ABC3
And also match column B if it shows "Yes" then show "Correct" or "Not Correct".
View 3 Replies
View Related
Oct 23, 2008
In column A I have product names, in B I have product categories and in C:H the product categories from column B are titles and the name of the subcategory is written in the cell. Because there are mutlipule sub categories that a product can belong to the report produces mirrored lines with one sub category on each line. What I need to do is combine all of these subcategory entries into one cell separated by a comer (,).
View 2 Replies
View Related
Jan 28, 2013
I have two columns each column has a combination of the same 3 words "cash", "card", and "trade". The are formatted as "Cash & Card & Trade" or "Cash & Card" or "Card" ect. I want a formual that can combine the entries written into each columan into one column. I will put an example below.
A
B
C
D
1
Client
new
old
both (formula in this column)
2
name 1
Cash & Card & Trade
Cash & Card
Cash & Card & Trade
3
name 2
card
cash
Cash & Card
[code]....
View 3 Replies
View Related
Apr 15, 2009
Column A has the following values:
au:asd
au:qwe
au:zxc
etc
I want to combine the text from each cell into a text string so that each cell's text is separated by a comma, thus:
au:asd,au:qwe,au:zxc,etc
I can do this with a formula,
=a1&","&a2&","&a3
but this may exceed the 255 character limit when the text string needs to be transferred to a query.
View 9 Replies
View Related
Nov 30, 2006
I have 4 cells with text in them that I am trying to combine into one cell and not have any blank lines between the text. The cells are arranged like this:
A1="One"B1=Cell where the text is combined
A2="Two"
A3="Three"
A4="Four"
I set the cell alignment in B1 to wrap text and use the following formula in B1:
=IF(A1=””,””,A1)&IF(A2=””,””,CHAR(10)&A2)&IF(A3=””,””,CHAR(10)&A3)&IF(A4=””,””,CHAR(10)&A4)
The problem is if there isn’t anything in one of the cells in column A it makes a blank line between the text in column B. I am trying to get the text to the top of B1 and not have any blank lines between the lines of text. Is there a way to change the formula so that it will do the following three things:
1. If the cell A1 doesn’t have anything in it then don’t put the value in B1.
2. If A1 has something in it and A2 has something in it then put the text of A1 in B1 and HAVE a carriage return after the A1 text.
3. If A1 has something in it and A2 does not have anything in it then put the text of A1 in B1 but DO NOT have a carriage return after it.
View 7 Replies
View Related
May 8, 2009
I have the following VBA code and try to sum the range from B2 to CY2 for DA2; then B3 to CY3 for DA3, etc. Therefore I use the for loop. It results in "Application defined or Object defined error".
View 4 Replies
View Related
Sep 5, 2013
I need an Excel 2003 compatible solution for this. Formulae only, no Macros or VBA in this case. The limitation set by the organisation we report to.
On sheet LGFC I have clients with their attendance hours, notes and the relevant dates.
01/07/2013
01/07/2013
08/07/2013
08/07/2013
15/07/2013
15/07/2013
Ref No
Name Carer
Name Client
Active
[code]....
To really test if this works you need to extend this over more than one month. My sheet goes on for a full financial year and of course with rows of clients. I now need to report per month. However, next financial year the month column locations may vary.
We need to report per month.I am able to sum up the month's total hours for all clients (E3 to J26) with the following formula (which ignores text). Data!$G3 gives the relevant month in date format (1/7/2013).
=SUM(IF(ISNUMBER(LGFC!1:1),IF(LGFC!1:1-DAY(LGFC!1:1)+1=Data!$G3,LGFC!3:26)))
array formula: Ctrl Shift Enter
This results (example data) in 10 hours. (if I had a client 2 with zero hours, and a client 3 with another 10 hours it would result in 20 hours)
Now my next step would be to report any notes. So I want to find all the text in that month and concatenate it into one cell. I started with this formula, but that doesn't work and gives a FALSE.
Doesn't work!
=CONCATENATE(IF(ISNUMBER(LGFC!1:1),IF(LGFC!1:1-DAY(LGFC!1:1)+1=Data!G3,LGFC!3:26)))
array formula: Ctrl Shift Enter
Doesn't work!
Ideally I would not just want everything jumbled into one concatenation, but even that would be acceptable. (It is unlikely that there would be more than 255 cells with notes.)
Based on the above example the ideal result would look like:
1 Mrs Test, unwell, left early, Sick
But I would accept just the notes unwell, left early, Sick
It needs to ignore blank cells. With multiple clients it would ideally look like:
1 Mrs Test, unwell, left early, Sick
2 Mr Best, in hospital, visiting family, Sick
or
1 Mrs Test, unwell, left early, Sick / 2 Mr Best, in hospital, visiting family, Sick If we want to include the Ref No and Client Name it probably needs another if statement. "If there is text to concatenate that month/row than include Ref No and Client Name."
View 1 Replies
View Related
Dec 17, 2008
combine 2 cells containing text the convert all to lowercase
i.e. in say A1 John, B1 Smith
convert this to c1 johnsmith
this is to me used as a user name generator
then in cell d1 create a 5 digit unique number (password)
View 9 Replies
View Related
Jun 27, 2008
I have a data sheet which has a series of dates within it. So for eaxample the data sheet will have date of "01/07/2007" I then want to take this data and create the following field:
"June 2008 Pre Data" The problem I have is when I try to join my variables togeother the code crashes (Invalid Procedure call or argument).
The code is as follows ...
View 3 Replies
View Related
Apr 1, 2014
I am trying to build a list of account numbers based on conditions in a spreadsheet. Every account number will look like: XXXXXX-XXX-XXX, depending on if there is a 'Y' in both fields...see attached. I am desperate to get this done and have hit a complete road block.
View 2 Replies
View Related
Jun 20, 2014
How do I combine a formula and text in the same cell in excel 2010.
What I am looking for is the sum of a number of cells, followed by text.
e.g. something like =SUM(E4:E50)"/35"
If the sum result of cells E4:E50 was 10, I would be expecting to see visible in the cell '10/35'
How do I write this formula?
View 3 Replies
View Related
Oct 27, 2009
i need t oknow if i can have text added into 1 cell with having a formula with a answer in it as well,,,i have attached a sheet with better examples of what i mean.
View 3 Replies
View Related
