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 am my excel worksheet (excel 2010) I have one cell that changes every day (number). I want this number to open my htm document and replace the same number in a string in the htm and save/close this.
An example:
My htm document is located at C:/ and named XX.htm
The number I want from excel is in cell A1 in sheet1, and the worksheet is located in D:/ named yy.xlsx
And the text(number) I want to replace is in the following string in the htm document, in this string it is 72, next day it can be 30:
src="Bilder/72.png"
As you can see in attached file (hopefuly you can see it) pivot table Summed sales in 2014 and 2013 in column GRAND TOTAL. Insted of summing this two years, I would like to show difference between them, but keep this layout!
PROBLEM.jpg
I need in every column in every rows (that is in whole document ) change symbol, to, (that is before every comma, I need add back slash ).
View 1 Replies View Relatedi 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 have a data starting from column D. And the list goes like below:
D1: TO
E1: CC
F1: BCC
G1: Body
H1: Text1
I1: Text2
J1: Text3
I would like to obtain the final body in column K after replacing the text with column H, I, J.
Below is how my data looks and How i would like to obtain the final body.
To
CC
BCC
Body
Text1
Text2
Text3
Final Body
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.
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'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'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 have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).
number_forma.png
I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.
In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search): [>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";
custom_format.png
As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.
Here is a screenshot of what the original and custom formatted cells look like:
custom_formatted_cells.png
As you can see the custom formatting works and does exactly what I wan
The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).
To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:
CONCATENATE("SOME STRING ", B1)
Where B1 is the custom formatted cell.
The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:
concatenate_formula.png
As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".
I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)
My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?
*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.
I have a text document with 1000 lines. All the lines are in this format
important< %#$ other words
other important thing < %Z$different words
some important text < $# some other words
I want to know if is such a excel filter that will delete all the characters starting from "<" until the end of the line.
So, the output of this 1000 lines will look something like this:
important
other important thing
some important text
I want to know how to display part of a cells text value, inside another cell.
Suppose in cell A1 i have "20-Jun-14"
How would I get cell B2 to display just "Jun"?
I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.
View 5 Replies View RelatedI have been working on this worksheet part of which is attached herewith. I would like excel to automatically enter Updated/Inserted in column B against Individuals' names as per the instructions given in column A. For example: As per instruction in A9, B13:B16 should show Updated. I have tried to use the nested if function, but it does not work as I want it to. Also as I am not used to macros or VBA codes, could this problem be solved with formulas?
View 2 Replies View RelatedIs there any way to remove the first part of a string of text in a cell and save the second part?
The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.
The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?
I am trying to do a vlookup and am having soem issues. I have data like this in the cell i am using as the reference: "60030 - COLUMBUS, OH". The first column of the range I am searching would have "60030" in it. What i need to know is how do I get the vlookup to just reference the first 5 digits in the reference cell? As you can see by the formula below, I set the logical value to true hoping that it would find the closest value, but it did not work. =VLOOKUP(A7,sdoalign,3,TRUE)
View 2 Replies View RelatedI have a column of cells with similar to the following text.
First Marine Avenue 18 1303 11 1
There will always be a final 4 groups of text numbers, each with a space
between, but each having different number of numbers (this particular
sequence has 2,4,2,1 - but others may be different, but always 4 groups)
The true text ( a series of Road names are potentiaaly all different, having
a sequence of words that may be up to 5 words long before the numbers start)
I need to extract the 4 groups of numbers into seperate cells, leaving the
Road name text in a singe cell. I would then have a table of 5 columns, the
first the Road text, and the next 4 columns being the group of numbers, which
I would then format as numbers.
I am creating a sheet with the cell information coming from different cells. I want to bold and/or underline a part of the cell to add clarity.
For e.g.
Cell A1 = "Trans X" (Name of the transaction);
Cell B1 = "System Y" (System where the transaction exists)
Cell C1 = "Type Z" (Type of transaction)
I want to create a cell which has
Newsheet.Cell (x,y) =
"Name: Trans X
System: System Y
Type: Type Z"
I created the String by reading and concatenating the values, but I am not sure how to add the formatting to a part of the string.
I want to put text after firsr 15 chars to new row.
View 5 Replies View RelatedConsider the following:
Cell A1 has this text in it:
WT*495*001559369~G3**D1234567**XX**20070626*1740****DHLC*20070807~B4G*RX*1~C32*UDD**UAD~ITQ*05*3****20030711*12~N1*FF*LALALA*25*00802109480~N3*12A46 KRESSASLE
Is it possible to somehow filter, search, find, etc, and basically clean cell A1 so that in the end it has just G3**D1234567?
I made a search macro, but the problem is there is so much text that it takes 2 seconds per cell to do a find. There are 6 columns with 65000 rows. I assume if I clean up each cell and do a "xlWhole" instead of "xlPart" in the find code, it will be much faster.
A1 = WT*495*001559369~G3**D1234567**XX**20070626*1740****DHLC*20070807~B4G*RX*1~C32*UDD**UAD~ITQ*05*3****20030711*12~N1*FF*LALALA*25*00802109480~N3*12A46 KRESSASLE
Result:
A1 = G3**D1234567
Possible?
Note that all rows do have a "~G3**" before the actual number I am doing a search for.
in order to hyperlink a single peice of text in a cell and not the whole cell I have created a shape with no fill or line but added text. I then hyperlinked it to a location in the workbook. When I set the Move and size with cell property I am not geting the expected result. If I resize a row well above that cell, the object isn't following the cell it wa placed in. I would be happy with alternative mechanisms for putting independantly linked text.
View 4 Replies View RelatedI have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
Here's the situation (an example):
I have sheet1 that contains the following information
Column A Column B
company1 - book value 0,5
company1 - earnings 0.2
company1 - R&D 1111
company1 - total assets 200000
company2 - book value 2
company2 - earnings 333
company2 - total assets 12
So column B contains only numbers and note that for company 2 I do not have any numbers on R&D.
I have in sheet2 only once the company name. I would like now that if column a contains the text "book value" to have the value of column B pasted in sheet 2 column B. Same for earnings to be pasted in column C. R&D in column D and if R&D is not present for that company then the value should say something like unknown.
I am building a summary spreadsheet which will bring in a number of values from other spreadsheets located in folders on a network. Each set of external references will return the same cells, but the location and filenames are different. In order to make it quick and easy to modify the file names and locations without having to change the cell references in my summary sheet i want to type the full reference in a cell as text then use this in another cell as part of the external reference...
View 9 Replies View Related