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?
I need to know if it is possible to combine these 2 formulas (1. (RIGHT(C23,0)&RIGHT(C23,4)) and 2. LEFT(E23,2)&":"&RIGHT(E23,2)
into a single cell. I have a date time group that is in text format. I need to take the last 4 digits (the time) and pull it out into another. Then take that cell and convert it into an actual time ie 2:30.
example: 01may2014 0831 first formula pulls the 0831 into another cell. then the 2nd formula converts it into a time 8:31.
After this occurs, I can then subtract times from different categories in my spreadsheet.
My, problem, is that I cannot combine the two formulas. They work great by themselves. I am just trying to make formulas more compact and hopefully more efficient.
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 RelatedI've got problem with combining 3 cells into one. First cell contains text, second have date yyyy-mm-dd and last one is as previous. I'm using formula .
[Code] .....
And I'm getting: TEXT_41694_41701. How can that be corrected to use formatting for date format to get TEXT_yyyy-mm-dd_yyyy-mm-dd?
I am working in Excel 2010 (Windows 8) and am having real problems trying to find a function/formula that will work with both numbers and text. I am trying to identify if the contents of Col A cells and Col C cells on each row are duplicated within a worksheet, and if they are, to add a 'J'.
(Column A is a list of numerical company codes and Column C is a list of adviser codes using the letter A-G. If a company ID code has more than one of the same adviser codes referenced to it, it is termed 'Joint', hence the 'J').
The formula I've used is: =IF(A2:C2, FREQUENCY>1, "J")
But it tells me 'numbers only'.
My business I work for is a collection agency and from time to time our clients send us files that are beyond recognizable for our collection software to understand nonetheless import into our database.
The file was a PDF at first but I have since been able to convert to excel format.
The data isn't too scrambled just scattered throughout rows. I would like to have rows of patient data into one row.
The file after being converted was all in row A but I have since used text to columns to divide into separate rows. However now one patient information is on multiple rows and I need each patient info on one row.
The file looks like:
|A---------|B-------|C----------|
1.John-----|Doe-----|123 Main St|
2.New York-|NY------|
3.11100----|SSN----|Balance Owed|
I would like the data from multiple rows to continue on row 1 like this:
|A--------|B-----|C------------|D----------|E--|F-----|G----|H-------------|
1.John----|Doe---|123 Main St.-|New York---|NY-|11100|SSN-|Balance Owed--|
I am no programmer in the least and have very little to no experience in pivot tables.
This file has about 7000 accounts with patient info spread through 3-4 rows for each set of data.
How I could go about doing this without manually copying and pasting into additional columns on the same row. That will take hours if not days.
I would like to combine 2 columns, containing numbers on it, is it possible?
I have found the other option how to do it
=A2&B2 but then this doesn't work
I have
COL A COL B
12345 0000
The result I want would be= 000012345
I have three workbooks. Every workbook has 50 persons IT information. Those are CPU list, Monitor list, UPS list. I would like to create another workbook. Where all user's IT information will be available. Like X user's information required, under x information his CPU, monitor and UPS information link will be available. How to create link of the CPU, UPS monitor sheets with new workbook?
View 4 Replies View RelatedI have a data in sheet1 as below :
Excel 2012ABCDE1Sr. No.PriceSr. No.Price
21234535123453531234623123462341234712123471251234818123911861
234919135011971235023412350234812371251235125912352261235226101
2353271235327111235428123982812123552912355291312382301235630141
[Code] .........
And now looking for output in sheet2 as below :
Excel 2012ABCD1Sr. No.Price1Price2Total
21234535357031234623234641234712122451234818#N/A1861234919#N/A
1971235023423446881237125#N/A259123522626521012353272754111235428#N/A
[Code] ........
The sr.no. codes and price will keep changing in sheet1, it should automatically update in sheet2. Is it possible with either vba or formula?
I have a text box that is set to "=B1". B1 is a cell from an Access table import with memo format. The text memo is long, probably on average between 1000-2000 characters. I have set up the text box to automatically resize for the text, but the text stops at what I assume is 255 characters, even though cell B1 displays the entire memo.
How can I set it up so that this text box (or any other similar shape) displays the text and retains its resize/wrap property?
This is done in Excel 2010.
I have a wookbook with serial numbers in column A and barcodes in column B. There are 51940 rows in total.
Is there a way I can search through column A, and for each unique value found , concatenate all of the barcodes that relate to that serial number in column C?
Serial Numbers | Barcodes | Combined Values
-------------------------------------------------------
abc123 abc12300 abc12300,abc12301, etc etc
abc123 abc12301
def456 def45600 def45600, def45601, etc etc
ghi789 def45600
i am using Excel 2010.
i am trying to work on a database that has some errors that i want to correct the issue is that some of the cells have data that is listed last, first instead it needs to be listed into first last.
I can not seperate this into 2 fields as it would break the program this database belongs to.
this database is linked from excell to access i am using excell to update an access database
i use office 2010
I am using Excel 2010.
In my worksheet I have 'Column A' and 'Column B', In 'Column A' are product I.D. numbers. In 'Column B' is a text description of the product, whose I.D. number is in 'Column A,' and should also contain the I.D. number from 'Column A' somewhere in the midst of the descriptive text. However, some of these in 'Column B' do not.
I need to create a function that looks for the value in 'Column A' and determines whether or not it is present in the text of 'Column B'. Therefore, spitting the answer out in 'Column C' so that I can copy it down for 100,000 cells.
I'm running Excel 2010.
In a field containing this: Bergamot, Orange*(11,16) I want to put a space before the asterisk. There are hundreds of these in the spreadsheet with other text before and after the text to be replaced. I made sure the field is defined as a text field.
I searched on "*(" and said replaced with " *(" and ended up with " *(11,16)" - the Bergamot, Orange was removed from the field.
I'm using Excel 2010). I have column I, which is the sum of columns A and B (first and last names of patrons), and I have used conditional formatting to identify all duplicates in column I with red text. I would now like a macro to find red text in column I and then delete the corresponding rows.
I've been looking around on the web for a while and have cobbled together this code stolen from other people's macros:
[Code] ....
When I run it, I get the following error:
Run-time error '1004':Application-defined or object-defined error
Excel doc is called "stupid mailing list.xlsm", sheet I want to run the macro on is called "voters".
I want to edit phone numbers in format 1112224444 to 111-222-4444.
My best shot has been...
Excel 2010 (Mac)
Sub EditPhone1()
ActiveCell.Offset(0, 0).Select
With ActiveCell
.Value = .Characters(1, 3) & "-" & .Characters(4, 3) & "-" & .Characters(7, 4)
End With
End Sub
I've tried many variations, no luck.
Excel VBA 2010.
I have this problem:
e.g Cells ""
C2 - 128.50 g
C3 - 211.01 g
C4 - 198.50 g
C5 - 179.34 g
I need to split the text into
Cell "C2" = 128.50 and Cell "D2" = g
Cell "C3" = 211.01 and Cell "D2" = g
and so forth
I wanted to avoid using delimited method with space and induce an automated method to format every cell of Column "C" one by one.
Why the heck every time I enter in to the "edit text" area of the chart title box in a chart in Excel 2010 and type "=B27" (without the quotation marks, and understanding the contents of "B27" has the text I wish to display) does this idiot thing simply display "=B27" (again, without the quotation marks)???
View 4 Replies View RelatedI have a 2010 version of MS Excel. I have roughly 10000 cells that I need to separate into two columns from one cell.
Here is an example of one cell "John Smith 888-8888".
I am using Excel 2010. I am extracting text and data info from a single cell and inserting the info into different cells. Let's say the following formula is entered into cell M28:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), "")
This tests cell J28 to see if the text string "IM" is present in that cell.
If it is, then the text "IM" plus the following eight characters become the result in cell M28.
If the text string "IM" is not present in cell J28, I want the contents of cell M28, that is the formula itself, to be deleted from cell M28. EG below:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), function here to delete this formula from this cell)
( logical test ) ( value if true ) ( value if false )
Excel 2010. When the result of the formula in Col. E is less than zero, I need to move the row to another sheet, and then delete the row.
I modified another poster's script as follows:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Move rows with negative value in E to Flagged Items sheet
Dim rng As Range
' Set Target Range
Set rng = Target.Parent.Range("E2:E200")
[Code] ........
The script works perfectly if a negative value is manually entered into a cell in Col. E, but not when the result of the formula produces a negative number.
I can't find a way to make the Case Is < 0 act on the resultant value rather than the formula.
Is this possible in the script or is there another way to achieve the desired result?
I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.
View 1 Replies View RelatedI deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.
I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.
I use Excel 10 and i'm on Window 7.
I need a formula to transfer the values in on cell to another designated cell. "AM" should be in the cell under "AM and "PM" should be under the cell marked "PM". If there's nothing in the space where AM or PM is, that should be blank.
Two formulas which I need in Cells E74 and E75.
Basically the formulas will depend whether the entry in D73 are Yes or No
in cell E74, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF ‘D73’ = ‘Yes’ then E74 = E70
in cell E75, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF D73 = ‘Yes’ then E74 = E70
Excel version 2010
(Excel 2010). I have 3 cell that contain data. What I need is to compare the 3 cell and return the data that has different value into 1 new cell.
I have attach an example : test.xlsx
The formula I found is close but I need it to count, not sum.
=SUMIFS($C$36:$C$51,$B$36:$B$51,$B36)
Example data: [972 1] [972 2] [972 3] [214 1] [214 1] [765 1] [765 2]
I want the formula to count the different numbers in the second column based on the first column. The results should show, 972 equals 3, 214 equals 1, 765 equals 2.
I am trying to separate this out but it keeps putting this paragraph.
I have a large spreadsheet that I would like to indicate the location of the cells (Change cell color or text color) which contain formulas. My thought is that conditional formatting would be the easiest way...
View 3 Replies View RelatedI just upgraded to Excel2010 and noticed that if I calculate percentages resulting from other formulas, the result displays as a percentage with 15 decimal places and it ignores the format of the cell. My guess is that since formulas are being used in cells A1,B1, and C1, Excel is ignoring the format. When I hard code the numbers, I obtain the desired result.
Is there a way to change the default of 15 decimal places for formula results involving inputs that use formulas in their own creation? (outside of the Round function in each cell? Within Options?)
The data looks like this:
A1 = formula resulting in 1.76%
B1 = formula resulting in 1.90%
C1 = formula resulting in 1.69%
D1 = C1*B1/A1 resulting in 1.8218147382920100%
The desired result is 1.82%.
I am having a terrible time with Excel today. The version I'm using is Excel 2010.
I just want a simple division formula in a cell, formatted to accounting. This should be really easy, but it isn't reacting the normal way.
The simple division is =13588/12
The output in accounting format should be 1,132.33.
Every time I enter this formula into a cell that is pre-formatted to accounting, the result is 1132 1/3. It also erases my formula and replaces it with the value.