I have a cell that is acutally a concatination of several other cells. The formula is something like this....
=A1 & B1 & A1 & C1 & A1 & D1 & A1
Cell A1 contains the formula =char(13). This forces a line break between each data element.
My problem is that on some peoples machines the cell displays with a square at the end. I'm not sure why it does this only on some machines. Is there a better way to do this or is there a way to suppress the square?
I'm trying to validate a string that can have only dots and numbers, but there can't be two dots in a row. String has to begin with a number and end to a dot
So these are ok 1.1.1.1.1.1. and 2.452443.1.5.21.5.42131.
but 1..1. and 1.1.t. and .1.1. and 1.1 are not ok.
I have attached a sample file where I want to concatenate data in colums A2:H2 into one column with line breaks. But CHAR(10) function alonwith CONCATENATE is not working. I have formatted the destination cell for wrap text.
I want to create the user defined function in excel in which i will assign the value to a char and after that i should b able to use it in formula.
Eg: for "FL" i will assign 1 for "LF" i will assign 2 for "VV1" i will assign 3 for "VV2" i will assign 4
and so on.......then i want to use this to compare both figures like i will put the formula like if(VV1 < VV2,true,false) result should b true coz VV1 is having value of 3 and VV2 is having 4.
So basically how we create the custom lists i want to give the priority to my own list and want to use it in formula.
only grabs the first 255 characters and leaves the rest behind. I have around 200 workbooks with textboxes in them and I need to get them onto the worksheets themselves.
Is it possible to set a PasswordChar for an InputBox?
Basically, I have an input box asking for an admin password to make changes, and I'd like to make it so onlookers wouldnt be able to see the password. Any suggestions on how to do this?
I could make another userform and have the textbox have a PasswordChar, but I'd rather not.
I'm new to this, and trying to get a spreadsheet organised for my nba fantasy team. To save time, (and me having to enter the positions of 300ish people individually) I'm trying to pull out their positions into seperate colums.
The data I am trying to pull is from these:
Jarrett Jack, Ind PG Gerald Wallace, Cha SF, PF
=IF(FIND("PG",$B2),"PG","")
That is the formula I am trying to use, it finds the PG for some, but when it doesn't it gives me this "#VALUE!" in the cells, instead of a blank as I have tried to input.
Now, there are 5 combinations that I am trying to pull out: PG, SG, SF, PF and C.
There are multiple combinations that need to be pulled out, and placed into my 3 cells allocated (EG - 1 for Pg, 1 for SG, 1 for SF).
I have also found a flaw that takes the C from Cha (being the team) instead of from the position. Can I search for a individual C? I've tried to google these things, but I can't find an answer.
B4 is a date format and I need to copy each single character from B4 so that it goes into the corresponding cells from B7 through to B14. So it looks like this:
is there a way to automate =left(b1,40) and remove commas from the text?
text in the cell would be something like a name for instance john doe, LLC but some names are to long so was using the =left to reduce it to 40 characters and using find/replace for the commas replacing it with a space.
how I could split a cell's contents into two cells when it contains more than 1024 chars? The code would need to be clever enough to split the data after the nearest full stop before the 1024 char limit.
I need a slight mod to the below code which works perfectly otherwise. What it does is it divides whatever is in column K by 30 and puts in its relative cell in column M( There a bunch of mini tables below each other with blank rows in between).
The thing is sometimes there are characters in column K like "N/A" for instance and the macro crashes since its only designed to take into account numerical and blank cells in column K.
I would like to apply this formula to VBA and call to existing macro so that this is applied to all the cells in Col. A. How can I do that? Or, is there a better way to do it?
Buttom line, I'd like to see only the last 500 characters per cells in Col. A after I run the macro.
I need to store a known index, unknown value in a variable so I can increase the value and use it in a database. It is used as a version number for a part and the versions go like: "00" -> "AA" -> "AB" -> .... -> "AZ" -> "BA"... etc.
VB: If tool.Worksheets("TRB Database").Cells(A, "R").Value <> ThisWorkbook.Worksheets("Design Calculator, Q").Cells(7, "C").Value Or tool.Worksheets("TRB Database").Cells(A, "AA").Value <> ThisWorkbook.Worksheets("Design Calculator, Q").Cells(5, "K").Value Or tool.Worksheets("TRB Database")
[Code] ....
That is a part of the increase and when I try to increase "AA" by 1 it goes to "B" and not "AB".
I am having mucho problems because of Excel's 255 character limit when copying an entire spreadsheet to a new workbook.
In a nutshell, I copy a "data entry" page onto a report page within a workbook. The entire report page is then copied and moved into a new workbook. That allows me to have a stand alone, exported file outside of my main data entry tool. When you copy an entire page to a new workbook, Excel imposes a 255 character limit. Thus, I see truncated responses on the exported spreadsheet only.
This is really annoying, my formula is not picking up words starting with the letter "Y" it will pick "Y" up on its own but not if you start a word with that letter, see my attachment and change the word "Alpha" in column A to "Yoda" or something begining with "Y" and you will see what i mean.
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 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 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.
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
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 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.
(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).
