I have random descriptions that have the case dimentions within the text
The descriptions are NOT standard length.
the only thing I can think of is the dimensions in ALL descriptions have this in common:
Length ( x ) Width ( x ) Height
I am looking for the Height value
How can I use the =mid function based on the 2nd ( x ) character?
I have several property codes in column E e.g THA 134,THB 224, C 122, 223 AD. The letters A, B, C, D denote a type of property but do not always appear in the same place in the cell (sometimes 3rd character, sometimes first character etc). I want to put in a formula in column F, so that if the character A, B, C or D is in column E then the result in column F will be "A type", "B Type" etc....
View 9 Replies View Relatedif a cell contains "usable characters". By usable characters i mean i want something to check if a cell contains a number or letter.
cell = AAAAA formula returns true
cell = A123 formula = true
cell = 123 formula = true
cell = ----- formula = false
I am trying to build a formula that return what is after ":" For example, in a cell, I have NASDAQ:MSFT and I want another cell to return MSFT. I tried to use the function FIND combined with a LEFT or RIGHT but I could not have it work.
View 2 Replies View RelatedPretty sure this has been asked but have searched the forum to no avail, but I need to extract the numbers from a value which has a letter on the end.
eg. 1000x I need to get out the 1000
or 2p I need the 2
I have sooooo many values ranging in numbers of digits, so just basic left(A1,2) won't work, I'm sure I've seen a search or find function but don't know how to use them!
I need to make a list of Part Numbers in quotation marks. If a number of digits of a P/N is less than 13 a number of space characters has to be added to make the string 13 characters long.
We have example P/N:
1234567890123
12345678
123456
should become:
"1234567890123(no extra space characters should be filled-13chars)"
"12345678(5 extra space characters here)"
"123456(7 extra space characters)"
Is there a function that inputs a cerain number of a specific character ("space" in this example)?
Is there a function in VBA that is similar to either the FIND function or SEARCH function in Excel? The arguments for the FIND function in Excel are FIND(find_text, within_text, [position]).
I have a text string in VBA ("$A1:$D$13") that I want to be able to identify the first "$" and then later the ":". I'm getting tripped up on the 3rd line of code. Thanks a million.
Sub page_set_print_area()
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$13"
x = ActiveSheet.PageSetup.PrintArea
Position$ = Search("$", x, 0)
End Sub
Does such function exist in Excel?
View 9 Replies View RelatedI have special character that I removed with =CLEAN formula.
It was only one character which represents carriage return. It looks like one little square with question mark inside.
After I applied =CLEAN formula it disappeared, but now I don't have space between these two words.
How could I replace this special character with space?
I want to use the find function on a range and search for a "," character. If I get a "," I want to return a True otherwise false.
I tried just recording a macro using the find function but no code shows up.
how to use the find feature in a macro, can this be done?
excel 2010
I would like to create a macro that deletes all rows starting with an open parenthensis in column B. The contents of what is in parenthis varies from 2 letters up - so anything starting with a parenthis is sufficient criteria.
After the initial deleting (mentioned above), would also like to delete (from column B again), rows containing specific, multiple phrases.
None of the functions I have see so far will facilitate this...
We do large loads of new part #'s/descriptions/UPC's/etc from excel into our database. The fields are limited on character count and I would like writing a formula to break the contents of a cell based on character count without breaking a word.
Examples
Column A Current Data
Row1: 12Pt HD Offset Striking Wrench 1-3/8" & 35MM (44 characters)
Row2: 12Pt 1/2" Drv Socket 27MM (25 characters)
Row3: 12Pt Satin Combo Wrench 11/16" (30 characters)
Row4: 12Pt 1/2" Drv Deep Socket 1-1/8" (32 characters)
Results I'm Looking For
Row1: Column B-12Pt HD Offset Striking Wrench Column C- 1-3/8" & 35MM
Row2: Column B-12Pt 1/2" Drv Socket 27MM Column C-No Data Returned
Row3: Column B-12Pt Satin Combo Wrench 11/16" Column C-No Data Returned
Row4: Column B-12Pt 1/2" Drv Deep Socket Column C-1-1/8"
INVENTORY-TRACKER.xlsx
I have a attached an inventory spreadsheet that I use at work. The only active column that gets data entered into it, is column E "Qty (Pcs)". I enter quantities of inventory in this column.
In column B is the part numbers for product. The letters at the end of the part numbers are associated with certain colors that you can see in the small chart to the right of the report. H=Yellow, M=Blue, S=Green, V=Orange and P=Purple.
What I am trying to achieve is when I type a value in the Qty (Pcs) row. I would love for the entire row that contains data to highlight in the respective color based on what letter is at the end of the part number in column B.
Example - I type 100 in E2 and the entire row of data turns Orange because the V at the end of 13001 is associated with Orange.
I want to develop a macro for getting filename without extension and seperating the value into two variables.
Requirement:
I have a folder with 3 subfolders inside.
Each subfolder has few files in it.
I will give main folder as input and it should check all subfolders/main folder for files and take that files and modify as shown in below example:
Example of one file: TEST-123_REV00.pdf
Here TEST-123 is the document name
REV00 is the revision of the document
pdf is as usual the extension.
Now my requirement is to prepare an output file with all these files in a text file.
The output is in this format..
Document Name | Revision Name.
I need a formula that splits a cells contents based on a character. Example:
LEATHER GLOVES PAIRS - LARGE PART#4000-0501
I want a forumla that returns everything right of the #. the number of charters changes so i need to reference the #
I am trying to pull certain members loads from our global design force spreadsheet and because of the naming convention we used for our structural members the 2nd to last character is unique to what I am looking for. I am trying to get a simple condition statement that will display the load only if the 2nd to last character of the name of the element is satisfied.
For Example, if "T" is what I am looking for then :
EDT4 will be true and give me the value accordingly
D10T1 Will also be true and return me the value
D10B2 Will not be true
I know how to use the Mid() and right() function, but I need to check the 2nd from the right.
I have a text box that is set to "=B1". B1 is a cell from an Access table import with memo format. The text memo is long, probably on average between 1000-2000 characters. I have set up the text box to automatically resize for the text, but the text stops at what I assume is 255 characters, even though cell B1 displays the entire memo.
How can I set it up so that this text box (or any other similar shape) displays the text and retains its resize/wrap property?
This is done in Excel 2010.
To all sifus out there, how can i transfer from these:
NAS517-3-2
-41353913
NAS517-3-5
NAS517-3-4
-42MS27253-2
-43353908
-44357182
To these:
NAS517-3-2
353913
NAS517-3-5
NAS517-3-4
MS27253-2
353908
357182
I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?
Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.
I have a cell which will contain SER01+SER02+SER03
and what i need it to contain is [SER01]+[SER02]+[SER03]
and shocker is i've got this to work for the first instance but not the other two
code as below... be grateful for your help
Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
Need a formula/code that will determine what the corrected part number should be (insert dashes if they are missing) by comparing to other values in the list.
Original A1:A5 = {452, a-bc, 123, 4-52, abc}
Corrected C1:C5 = {4-52, a-bc, 123, 4-52, a-bc}
I can do this using an intermediate working column, but can this be done all in one formula, or via VBA?
My formulaic solution is thus: .....
The following is my existing formula (I admit it is probably very far from what it should be) but I'm looking a way to base the conditional part of a function based on the text within a cell. =IF(E4=OR("WordA","WordB"),(I4),(-1*I4))
There are four words that I use to classify the column "E" for sake of simplicity I will name them as "WordA" "WordB" "WordC" and "WordD". Those words serve as the identifier that I am trying to test. I.E. if "WordA" or "WordB" are present in column E, I want the end result (listed in column I) to be a positive number. If "WordC" or "WordD" are present in column E, I want the end result listed in column I to be a negative number. Currently I have a formula in column I, which is: =((H3-G3)/G3).
Both Column H and G have numerical values. Essentially the whole goal of my process is that if WordA or WordB show up in E, then I want my formula in I to remain "as-is". If however, WordC or WordD are present, I want the end result or formula in column I to be negative.
I am not tied down or restricted to using a particular formula, but just don't know how to set it up either way. Additionally, I plan to copy the formula down in terms of numbers... i.e. I5,I6,I7, etc. so when submitting any advice or if supplying a formula to paste.
I have a numerical value of in cell B3. A user will type a value into cell C3. If the user enters a value other than the value in B3, I would like a space and a red X to appear just after the value they enter, in red. (The value is black; the X is red.)
Example: 100 X
I'm trying to obtain a result based on the number of characters in a cell. If the cell content is blank or contains 9 digits then leave blank.
Any other result must provide the outcome of "incorrect number supplied".
I've tried the following formula but it doesn't work.
I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.
In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.
Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "$240.00".
I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.
I need help on using the If function. Basically I am trying to calculate the cost of shipments. For example, cost of sending 1 box is 150 (eg cell A3), 2 boxes is 280 (cell A4), 3 boxes is 400 (A5), 4 boxes is 500, etc upto to 10 boxes (cell A12).
What I am looking for is cell A1 to provide me the cost based on the manually entered value of cell A2, i.e. if I enter a number between 1 to 10 in cell A2, then A1 should return a number from A3 to A12, e.g. enter 3 in A2 then A1 should return 400.
Is there such a formula or do I have to enter manually the amounts into an If function?
copying data from one sheet to another if it meets a certain criteria. I have a long aged debtors report that has several different cost centres and I want to split it out on to separate tabs for each centre. I want a function or marco that will go to row 1 and check if it equals the cost centre name on the other tab in a cell and then copy it and paste it in that tab, if not then it moves down to the next and the next until it gets one that does equal it and then copies it again.
View 1 Replies View RelatedA brief rundown on what I am trying to create. My family and I have a NFL football game we do every year to see who can correctly guess the most winners over the course of the season and then the ultimate winner receives a prize at the end of the season. After years of doing this manually, I decided to try and put my Excel skills to use and create a more "automated" worksheet.
After some heavy research, I was able to successfully create a user defined VBA which would calculate a "win" every time a cell is highlighted (in my case the winning team's name). For example, I highlight New England Patriots and when the formula calculates each person receives 1 in their win column.
However, I have now reached a problem I failed to think of. The formula does not take into consideration the picks everyone is going to make. Not everyone is going to select the winning team, so the current formula I have is giving credit for every highlight. Is there a way to add an if statement to this? For example, if person A selected New England Patriots and it's highlighted then give 1, if not give 0?
I know it's usually hard to base a formula off a cell color.
For some reason, I cannot attach the spreadsheet I am currently using. For a brief overview, cell M1 contains the color which I would use to highlight the cells in the range A2:H17. This formula is in cells B21 - B27.
The VBA which I input was:
Function CountCcolor(range_data As range, criteria As range) As Long Dim datax As range Dim xcolor As Longxcolor = criteria.Interior.ColorIndexFor Each datax In range_data If datax.Interior.ColorIndex = xcolor Then CountCcolor = CountCcolor + 1 End IfNext dataxEnd Function
Where the picks which are made should be logged. Whether each person gets a separate color and the VBA is based on that, or like I alluded to above if there is an X here and it's highlighted or something.
I have 20 sheets in a work book (1-20) with similar row details(80 rows)...but the columns have amounts for 12 months. I would like to do a sum if function based on months chosen.
For eg: If I select "sheet 1" and choose month as june...I want the sum of amounts from Jan-June.