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.
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.
Have a worksheet Pricelist, require to pull data from the columns to a new worksheet only if qty is more than 0, and delete empty rows afterwards. Required result is in worksheet order. Original file is about 10K rows.
I need to get the count of a string from multiple columns. The problem is that string1 exists in column A and String2 can exist in one of eight columns. See the example below.
What I want to do is use two search criteria. I want to find the number of times I have Type=A and if the row has a case Beta. In the example above I should get an answer of 2. (From rows 3 and 9)
I tried countifs but I cant seem to get it working properly.
I am working on a report but I need to split of text cells that contain text and numbers. I had worked in some formulas and I got stuck again when I drag down the formulas. In the attached file, you will see two tables. The one in the left is my current job. In the table of the right is how the table must look like.
The table is complete and will give you the whole idea.
Basically, I need to split the cell into three categories that are "Family", "Model" and "Phase". In example:
Cell Family Model Phase CCA CCA DC50X DC50X DCX3300CRDDCX3300CRD DPC2434 DPC2434 DCT-1700DCT1700 DCT-1800DCT1800 DCT-1800P3DCT1800P3 DCT-1800P4DCT1800P4
I have a spreadsheet that gathers information from multiple cells and strings the data into a long string - each piece of data broken by a comma in the string.
In another spreadsheet, I copy this data string into column A on sheet 1 - starting on row 2 and down.
On sheet 2 - and this is where I need help - I would like to have a macro that will take each data string on Sheet 1 column A and parse it out into multiple columns but the same row on Sheet 2.
ColumnA Row2 Sheet1 -> changing to many columns on Sheet2 but still on the same row.
The data string always represents the same number of data components - its just a matter of breaking it out and placing each data piece into its own column.
As enclosed in my workbook,I want to separate the numbers between two strings of which one is in the left hand side and other is in mid.The data is in Column D.The simple way to understand is that,
Total Amount = Amount X Exchange Rate
Total Amount is in Column B, where as "Amount X Exchange Rate" is in combined form in column D.Yes, this I want to separate i.e Amount separately column and Exchange Rate in separate column.I have shown one expected result in column E,F.There are certain characters like ],= which you have to not to take any amount after ] or =.The currency is somewhere,$/$$/INR/IRS,etc.
I have a field that contains rows of data, each made up of a Number followed by Text. The length of the numbers differ - sometimes 4 digits, sometimes 5 or 6. Similarly, the text differs by word-count. An example is
2546 Nags Head 75698 Dog & Duck 634 Crown
I want a way of dividing the numbers from the text (numbers in one column and text in another).
Standard 'Text to Columns' won't work: I can't use 'Fixed Width' due to the number-length varying, and I can't use 'Delimited' and [space] as it will then split all the text up word-for-word (concatenating them back will take ages as my list is 480 rows long).
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.
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’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
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.
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells
The data I require is :-
(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson
(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)
(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting
See examples 1 & 2 below
Example 1
CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting
Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before
Example 2
CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers
Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.
As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text
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.
This example is A1 to F4. There are two inputs; A10 and A11, and one output (formula) on A12. A10 will be the column I choose, and A11 is the row I choose...but it's chosen if the number is >= column A and < column B.
Example: I input A10 as 2, and A11 as 87, A12 will output 4. If I input A10 as 0, A11 as 85, A12 will output 6.
I kind of understand how to use VLOOKUP for this, but I am unable to check column A and B to choose the row.
I created a macro that opens a page as an excel sheet, grabs data from it, and inserts it into a cell.
My problem is that one of the cells has an abundance of data that I don't need.
The data that it puts into the cell looks like this: 0849940222,* Bill Myers ,* Thomas Nelson,*1997-10-02 The only thing I need is the first name that shows up.
The number at the beginning is always the same length, so I thought about using a trim function for that, but since names are not always the same length, how to do the other end. Maybe get the value for commas and delete everything before the first and after the second...I also don't know how to do that in VBA.