I'm working with a lot of documents where I need to replace numberID:s with words.
I have a table of 2 columns, where each number has a corresponding number that it should be translated to.
For instace:
1 - cpu
2 - fan
3 - bus
Is it possible to use this list to automatically Replace all the numbers in a document with their corresponding words?
(Without having to spend time on manually program some kind of macro)
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"
Basically I've got a back end worksheet with a huge table full of data (hundreds of rows, tens of columns) containing the data for a load of different contracts (each contract is on a different row).
I have all the information I want displayed on a front sheet by means of very simple lookups which looks for one contract at a time to display that information. What I would like to do is to be able to alter that information on the front sheet which will then go back and lookup that entry in the big table and overwrite the old data with the new. Effectively I want the excel sheet to act as a user form, but without actually using a form (a requirement from the people I'm doing this for). I'm struggling to find out how to do this as I don't know which VB functions I would need to use.
Each contract has its own unique reference number, so really what I need is some sort of code that will look for that reference number in the back end table, then look for any differences between what's on the front sheet and on the back end, and then replace anything that's changed.
One of my friend send me a problem. I got frustrated and scold him lot to do work like this but then I tried to work on it. Although my Vb experience is not good enough.
The problem is this that we have different things written in cells in column A like this “SET-EDT SPRAY 3.4 OZ & BODY LOTION 3.4 OZ & TOILETRY BAG “ and in some cells “EDT SPRAY 1 OZ”
Now we want to replace value Oz with value Oz / (Value * 3.75) ml. the below example will clarify the situation: ...
I'm currently building a large spreadsheet which will require numeric codes to be within certain cells. At present these cells are filled with alphabetical codes and the person using them has little knowledge of the numbers, is there a simple way for them to type in the alphabetical code and it to then auto-replace it with the numberical?
View 8 Replies View RelatedI 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 RelatedReplacing text with formula
I have this so far
I have WS with over 100 text boxes. Many of them contain text "Current Program".
I would like to replace text "Current Program" in all these Textboxes with text "Testing".
How can I do it without opening each Textbox and replacing text manually?
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'm making a vba macro that takes a workbook and splits and saves it as individual sheets. In this process I need to find all the referenced cells (ie =E5) and change them to their text or numeric value. A few things I've found on the form and tired with no success:
Selection is a column selected from the top to the last used cell (I know there is only 4 columns that have references)
VB:
Selection.Copy
Selection.PasteSpecial Paste = xlPasteValues
Also tried:
VB:
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Value
I feel like one of these should be working. But even after the paste the values are still references.
Edit: An afterthought, if I were to select the whole sheet and do a copy/special paste would it replace all formula cells with their calculated value?
I have a drop down list of locations, which when one is selected I would like to show an abbreviated version in a different cell (say B5). I have tried the following so far:
=IF(A5 = "London", "LON")
But I would need to do this for around 30 locations and would need the abbreviated version in the same cell.
I am creating a template for formatting data that I download from a wholesaler to upload to a platform. The large database of around 1300 records that comes with category and subcategory names, among all the rest of the data, that I download There are more column titles and information than I need, so my template will have blank headings that I will know to simply delete after I paste. Maybe there is a Macro for that too.
The category and subcategory names to appear in certain columns. I want those names to appear in multiple columns within the same records. How to create a formula/macro to copy all of the data in one column to a different column with a different heading and keep those headings different and save them within my template. I have a list of the 240 with CategoryID, ParentID, Category and Subcategory all corresponding. These always come and go in CSV format, so I cannot create additional sheets to save within my template without a hassle.
In two ID number value columns, I would like all of the text names replaced with corresponding numbers for upload to the platform that requires them. I have been trying to figure out the best way to create a macro to save within my template for daily use, but so far I have not been able to figure it out. I have been watching videos on vlookup, index & match, find & replace...I have not seen what I need just yet
OK, SO HERE IS MY PRODUCT TEMPLATE SO FAR: [URL]
I have inherited a spreadsheet that will prompt the user for a text file to import and it will split each line into different cells based on column widths. The problem is that one field can contain non-printable characters that are causing the macro to split the data into 2 lines.
I think one of the special characters is a new paragraph but there could be others.
I need help with replacing these special characters with a space prior to spliting the line into an array.
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.
refer to the attached workbook for reference. I am looking for a function in Sheet1, Column E that will search for the value of Sheet1, Column A within Sheet2, Column A. When a match is found, the function should look across Sheet2, Columns B - V for values of 1. When such values are found, the function should return the associated value from Sheet2, Row 2. There may be multiple values of 1, and as such, the function should separate values with a comma.
View 3 Replies View RelatedTable 1:
I like to go to Berlin.
You like to move to Amsterdam.
They want to see Chicago.
When will the reach San Francisco.
Table 2:
Amsterdam
Chicago
San Francisco
Berlin
What I want to do:
I want my function to see if the text in Table 1 contains a value in Table 2, and if yes, output that value.
Desired Result:
Row A1:
I like to go to Berlin.
Row A2:
Berlin
Row B1:
You like to move to Amsterdam.
Row B2:
Amsterdam
I have set up a forecasting module for my company, which I update on a weekly basis.
Basically, I centered my forecast on an 8 week average. Using all of the various equations in a regression, the 8 week series of data is used close to 500,000 times for each week’s forecast. What I am trying to do is move the 8 week series (A#:G#) and change it to the next set of cells (B#:H#) without having to do it using a filter and change all of the various instances of the occurrence. Because of the nature of the forecast, I will be "rolling" it forward every week, so to be able to do this all at once each week would save me a bunch of time. I tried using the Find/Replace fn by having it use the * symbol in place of the #...and this worked when I had it Find the data, but when I tried to use it to Replace the data, it reported an error?
I have series of companies and output ,some values of output are missed how can be replaced by the average of nearst two years values of output or replaced by the following or previous value of output,sample of my data is attached.
View 4 Replies View Relatedhow i would remove telephone numbers in a particular spreadsheet which are preceded with 44 which need to be removed and then replaced with a zero.
eg. 441234567890
I want to be able to replace the bolded and underlined areas of this javascript i have pasted in a cell with the value located in another cell. This means that those numbers would be replaced by a similar thing located in another cell. how can this be done? The way I would think you would do this by defining that say =b8 (an example of where the data could be coming from is ann excel formula, but not sure how to do this ...
View 6 Replies View RelatedWhat it does is to activate the desired worksheet, and for 3 different choices replace what's there with what I want to be there. I put this together a while back, and it's working fine, but I'm sure it can be cleaned up a bit. Any help would be appreciated.
View 9 Replies View RelatedI would like to set up a worksheet where once an ID number is scanned, the next cell over is populated with that person's name. (i.e. Johnny scans his ID 234567 and in the next cell, "Johnny Smith" is filled in. I have a worksheet with the ID numbers in one column and names in another, I just don't know how to get a second worksheet with a formula that will allow me to do that.
View 2 Replies View RelatedI need a formula that will replace a dollar amount with an X. In other words...I have 10,000 cels and half of them have dollar amounts in them...I would like to replace them with the letter X without having to manually do it...
View 9 Replies View RelatedI am a not too good programmer, but I construct chemotherapy regimens for my medical department. They are used to minimise the risk of wrong dosing to patients, etc. Every regimen is an .xlt file, and about a third of them (there are about 100) generate, in addition to pharmaceutical prescription, automatic letters to patients (for those regimens where patients administer oral dosing at home).
These patient-letters have the hospital's logo on them (the logo is a "shape").
However, the problem is that another hospital also wants to use my system. So I will have to replace all these logos with the other hospital's. I would like to do this in an automated way. I have received the other hospital's logo also as a shape.
I have already constructed a data-inserting macro in a separate file. This macro systematically opens each xlt file and replaces the text in key locations according to the file the macro is in (geographical names, etc.).
Is it possible to automatically replace the logo-shapes in a similar way, with these additional facts/wishes in mind:
1. There is always only one type of shape (a logo) in the xlt files.
2. Even though the shapes are exactly the same, it is possible the names are different.
3. The exact location for the shape is different in each xlt file, so I'd like the replacement to get the same top left coordinate as the one it replaces.
I have constructed 2 different files with the same data-inserting macro, each containing one of the logos (so I can change back to my own hospital) and geographic data. When the data-inserting macro is used, I'd like it to identify the shape in the data-inserting file (there's only one shape in this file), copy it, then identify the shape(s) in the present xlt file, and then replace these, in the exact same location.
can anybody write a macro code for the following: We want to have five values representing operative time in a row with a mean in the sixth position. The hard part is to get a macro that will allow us to put in a new value in the first column and have it shift the other values to the right and dump the existing fifth value and come up with a new mean.
View 9 Replies View RelatedHave a column of cells (521 rows), on the column each cell contains a group of number
Example:
27, 539, 484, 485, 506, 594, 523, 502, 505, 525, 541, 526, 527, 529, 542, 533
28, 540, 493, 494, 506, 594, 523, 502, 509, 525, 541, 526, 527, 529, 542, 530
28, 540, 493, 494, 506, 594, 523, 502, 509, 525, 541, 526, 527, 529, 542, 533
Let's say I would like replace 27 to 163 but when I find and replay the "527" also changed to 5163. How can i filtered out 527 and just change the 27. I tried Match Entire Cell Content, it doesn't work.
I have a long list of locations in an excel spreadsheet, stating either the country (eg India) or for US locations the state (in a two letter format, eg NY). I need to change all the state location to say "USA". How can I do this, without doing 50 find and replaces? Can I do something based around the fact all the USA locations are two letters, whereas all the others are longer?
View 4 Replies View RelatedI have a file with some statistics on how agents at work performed. This file gets sent to all of them, but before we send it we change the names for a number, so that they don't know how the other agents are doing (avoiding comparisons). People had been doing this manually, but it's getting really complex as the number of agents is increasing. I know that this can be automatized, but I have no idea on how to do it. A simple formula with an IF statement wouldn't be sufficient. Here's an example of the data. The file that is to be modified to be sent to all the agents looks like this:
View 3 Replies View RelatedI have 2 colums like this. And I want whenever there is a reference like this âë. "some word" in column 2, to have the macro look up some word entry in column 1, find the text in the same row in column 2 and replace the âë. "some word" reference with the text found in column 2. (See attached, sheet 1 as it is, sheet 2 as I want it to be).
Sheet 1
another wordâë. "some word"
awordìåôÜöñáóç
bwordâë. "aword"
some wordêÜðïéá ëÝîç
Sheet 2
another wordêÜðïéá ëÝîç
awordìåôÜöñáóç
bwordìåôÜöñáóç
some wordêÜðïéá ëÝîç
I am currently trying to get a formula to be added into a range after a piece of code is run.
This is because every time my code has finished, the formula somehow changes and I cannot get it to stop changing values.
I have the formulas further down my sheet, without the = so that it doesn't return a value.
In VBA I have tried something along the lines of Range("A1").Formula = "=" and Range("Z1")
Where A1 is the cell I want the formula in and Z1 contains the formula without an =
It has not updated the cells and they still have the error that I am trying to fix.