Macro For Removing Contents Within Square Brackets
Feb 22, 2012
(Split cell into multiple rows) I need a macro to remove contents within brackets in a cell.
Example:
A1 contains
[something, separated; a, couple of times; like, this] With, some; other, text
The result should be:
With, some; other, text
I want the macro to remove the brackets and everything within it for all marked cells. (I do not want to use search and replace function since I got a lot of these data and needs to repeat this procedure).
My code works great on my computer but when I bring it to another computer it gives me errors. I think it has to do with my use of square brackets to reference cells. the error that comes up is
compile error: can't find project or library
What I'm doin here is going to my template sheet then getting the info from different cells and then later using it somewhere else. in place of using range and selecting cell I am using square brackets to select a cell. it worked fine on my computer and was a lot less writing, here is a small part of my code
I need a formula to tell me whether a number is a square number, or I can do it so to see if the square root of a number is an interger. So far Iv been doing
A1 = 2 A2 = AND(RIGHT(A1,1)=INT(A1))
True or false outcome is fine, and it has been working fine on some examples, but the problem comes when I have 49, as it is 2 digits long. I've seen a formula similar to find the root symbol, and look at all the numbers upto this point (maybe FIND).
I have a small problem that I may be able to deal with in another way, but if all else fails then I need some assistance.
I have this formula: =AND(N3<=$U$1,MOD($U$1,N3)=0)
But unfortunately there will be a time when cell U1 will have the contents of perhaps 12(1). Is there any way to get the formula to ignore the brackets and the figures in the brackets?
In a large range of cells, how I can remove the single quote at the beginning of the contents of each cell? (without single quote they would be formulas). It didn't work using CTRL + L and trying to replace the single quote by blank applying this to the whole range. I am currently using Excel 2003.
Example:
cell a1 contents: '= IF (I17 = "----------", "----------", E17-C17) cell a2 contents: '= IF (I18 = "----------", "----------", E18-C18) cell a3 contents: '= IF (I19 = "----------", "----------", E19-C19) .... etc., etc., etc.
However, with this macro I am wanting to remove the Input Box that pops up and requires the user to hit 'enter' everytime the macro runs. Instead, I would like the macro to automatically insert the rows based upon the value of cell A1 without having to confirm with the Input Box.
Code: Sub InsertRowsAndFillFormulas_caller() Call InsertRowsAndFillFormulas End Sub
[Code]....
Because I am such a novice with coding, if the response could be in a form where the entire macro code is spelled out on what needs to be changed. That way I can just copy and paste the macro response instead of having to try and figure out how to apply your suggestions to my existing code.
I am doing a cross between two parents, whose possible genotypes are AA, BB, or AB. I want to use if clauses to look at the parent genotypes, (paternal is listed in column E and maternal is listed in column F) and then give me the possible recombinant genotypes in column G. there are 9 possible combinations (recombinants in parenthesis)
AA x AA (AA) BB x BB (BB) AA x AB (AA, AB) AB x AA (AA, AB) BB x AB (BB, AB) AB x BB (BB, AB) AB x AB (AA, AB, BB) AA x BB (AB) BB x AA (AB)
I tried using if clauses, but i don't think i can nest more than 7 cases, and I have 9 different combos.
I want to create a macro that will cut and paste a worksheet out of an existing workbook and tidy up the table by removing all rows where there is no value in one of the fields (product or part number for example).
I have this Macro that removes the selected items from the listbox1 and sheet called "Form" the only problem with that is that I have another sheet with a formulas in it that referes to the cells/ranges in sheet "Form" and so when I remove the range in "Form" worksheet the range still exists in my other worksheet where the formulas are and this is causing the errors.
What modifications I need to make to my macro to be able to delete data/ranges from sheet "Form" and sheet "Training Cost" ? so if I delete range A5:D5 in sheet Form it will also delete range A5:D5 in Training Cost.
I incorporated more codes to the ones that were just solved from this board, but how can I make the active cell stay on A1 of sheet "hypo_tax_dropdown"? Also, I obtained the codes for removing the duplicates from macro recording, will these codes work on any machine? I noticed that it doesn't have worksheet.function
Sub Macro1() Dim X As Long Sheets("Hypo_tax").Select
I have 6 rows A1-A6(sheetsize), they can be 1 of 8 numbers. (16,14,12,10, 5/8, 1/2, 3/8, 3/16) these are all metal gauges. The cell next to them B1-B6(sheetgauge) has a number that refers to how much sq ft each of these sheet metal sizes has. I would like a cell below A30 to add up all the sq ft in B1-B6 if it is 16 gauge. Then A31 will add up all the 14 gauge, etc.
How is this possible without having a huge formula.
Here is my standard formula, but it wont add the B cells together if more than one of the same sheet sizes.
where i copy up to 1000 products ID's like this: "1234567890 AA11111"(ofcourse there is allways differend code for every product its just a example :)) and paste it to exel, and the problem is i need only the 10-digit number (it's allways 10-digit number but the second one is sometimes diffrent) so it is possible to make makro that will remove every character after 10th character ?
An address is separated by the square characters, and usually I would use Text to columns>Delimited>Other>Alt+0010 to separate the lines. When I get to the Delimited screen where you check Other and enter Alt+0010, the keyboard just makes beeping noises and the text preview only shows the part of the address prior to the soft return, as if there is no char(10) delimitation.
Sheet1 I11299 S Taylor St. #12 Shakopee, MN 553792880 Tuva Rd. Cokato, MN 5532131381 360th Ave Siren, WI 5487245824 Dupoun Ave. N Brooklyn Center, MN 554305718 Garden Lane #2 Shakopee, MN 553796583 Hall Ave St. Paul, MN 5510771615 South 4th St. #2003 Minneapolis, MN 5545481525 6th Ave S #8 St. Cloud, MN 563019715 S Glen DR Bloomington, MN 5542010340 2nd Ave. S. #344 Waite Park, MN 56387 Excel tables to the web >> Excel Jeanie HTML 4
I created a macro to create the beginning of a pivot table- just the rows and slicers, because when I tried making a longer macro to create the entire pivot table (formatting, etc) I couldn't get the macro to work.
So I do get all the vertical rows I want, and I do get all the slicers I want. But I get blank space. I think this is because the pivot table is created from another tab, and the length of data in that tab will vary from file to file (I'm doing another 30 or so of these, all with varying amounts of data, but the same data categories).
Anyway, I get slicers, but in addition to getting the categories I want, I also get "(blank)" below the names in the slicers. Is there a way to add some code to the macro to remove the blanks so I don't get this field?
I have copied and pasted the macro below, with identifying data changed.
But when i click in the cell to edit the formula the {} (at the beginning and end) disapear and the formula no longer works. If i add them back manually the formula still does not work.
How can i edit these formulas without stopping them working.
I found a template at microsoftofficeonline.com it's just a bracket. I feel it has some underlying functionality, as they have a template in visio as well, but I can't figure it out.
So, what I'd like to do is have a workbook with say 100 worksheets. Each worksheet will have a person's bracket. Each person will write in all their picks. Their will be a master bracket where the actual winners will go, as the results are posted. When the master bracket is updated, Excel will flip through each worksheet, checking to see if the win in the master bracket matches the individual bracket. Points will be awarded or not.
Can that be done? I've also done a google search and can't get anymore help. I know that the web has multitudes of free versions, which I'd use, the only problem is that people would hesitate to register. doing it in excel, on the office system, there's no need to register, your cpu login takes care of authentication.
I need a formula to automatically convert inches to square feet. I have =IF(G5>12,G5/144). and G5 is the cell used to enter your inch value. The formula wrks great, but only if you enter over 12 inches. I'm pretty sure Im on the right track, just need to know how to add in the part about if its less than 12 inches it should be multiplied by 12.
I'm a new member to the forum and have a question about extracting numbers from a string for a square footage calculation. I am trying to extract the two numbers of varying length on either side of an "x" within an alphanumeric string. As you can see from below, the only constant is that each string will contain an "x" (assume there will be no other "x"'s in the string). I am trying to achieve the following:
I try to make option buttons instead of round with a black dot but square box with check mark liked the check box. Box can be filled with color (light blue or other color). When the box is picked the fill change to white background.
I have 5 option boxs, I like to group them in one group because I have other groups using the option boxs. I want the check mak stayed in that group when it is picked.
I know this ain't going to be easy if at all do-able, but would anyone know if there can be a formula/s made to calculate the largest size "square" shape (e.g. all 4 corners at 90 deg.) that would fit in to a known size Quadrilateral?
Value A,B,C,D and Z are all known values as are the diagonal lengths between corners
I failed dismally in Geometry at High school, but how was I to know that 35 yrs later I would need to know this stuff!!
Please see diagram for an idea of what I'm trying to achieve.
I have a worksheet containing square carriage return symbols (see below).
EXAMPLES Eg1. Baked[]Beans on []Toast Eg2. Smoked[][]Salmon in Brine Eg3. Ice[][][] Cream [][]Cosmopolitan Eg4. Mixed[]Nuts[][]per kilo[][][][] Eg5. [][]Baby Shampoo[]Fragrance Free
This data was extracted from an SQL Database and dumped into Excel.
I would like to know if someone can suggest a script to replace all occurrences of [] with a space.
Once I perform this, I can then perform a Search/Replace function as follows:
Replace 4 spaces with 1 Space Replace 3 spaces with 1 Space Replace 2 spaces with 1 Space
Hopefully then, the final result will appear as follows:
Eg1. Baked Beans on Toast Eg2. Smoked Salmon in Brine Eg3. Ice Cream Cosmopolitan Eg4. Mixed Nuts per kilo Eg5. Baby Shampoo Fragrance Free
Eg5. is a little tricky because a space will appear at the beginning of the cell (see above). It would be good if there's a script to remove occurrences of this also.
How can I turn those static cells into volatile cells (I think that's what you call it)?
I basically want to select from the active cell (C9) to the beginning of the column (but leave out of the selection the header row) and then the adjacent cells to the right (D5:D9), but not with fixed cell references.
