I'm putting together a table which shows monthly costs versus Total costs. Is there a way I could use if two choose between two option dependent on the value of cell A1.
Example
A1 = "Totals", multiply Subtotal in A13 by 12.
A1 = anyother value multiply by 1.
Something like:-
=if(a1="totals",(a13*12), =if(a1<>""sum(a13*1)
=If(isError(find("-";a1));mid(a1;3;9);mid(left(a1;search("-";a1)-1);3;9))
where: a1=AN03048-12
and its result I wanted is the following: 03048 (great! )
But... what if I'd have ANN03048-12?
Then the result would be N03048...
How can I obtain only 03048 or whatever (but only numbers) avoiding previous letters? How can I only pick numbers without letters starting from the left beginning of the record? I'd like to get only numbers before the "-" symbol. I think I should add a function before or instead of the "3" number (which I highlighted in red) in the second mid() function
I have some samples below and I need to split them up.
CritValsMtrx(1, 1) = 8850216
CritValsMtrx(1, 2) = 10
CritValsMtrx(2, 1) = 8850832
CritValsMtrx(2, 2) = 5
The MID function to split the text as below and it works.
MID(A1,1,FIND(" (",A1,1)-1) returns "CritValMtrx"
I have tried the RIGHT function to split the text to have the followings but it didn't work RIGHT(A1,1,FIND(",",A1,1)-1).
"1)8850216"
"2) = 10"
"1) = 8850832"
"2) = 5"
In a nut shell, all I need is the text from the comma to the end of the string.
I have thousands of track titles into my music library that requires some organization. In this case below, I need to add a "" character to split the track number from its title. So, the problem remains at the lack of pattern in the text and the only reference it the beginning of the song title (but it also can begin with a text or a number).Text.jpg
From: 01 - It's A Long Way To The Top.mp3 >>>>>>>>>> To: 01It's A Long Way To The Top.mp3
From: 02-Crazy.mp3 >>>>>>>>>> To: 02Crazy.mp3
From: 3-01 Need Your Love.m4a >>>>>>>>>> To: 3-01Need Your Love.m4a
From: 10-13 Angels.mp3 >>>>>>>>>> To: 10-13Angels.mp3
From: 01 10_15 Saturday Night.m4a >>>>>>>>>> To: 0110:15 Saturday Night.m4a
From: 3 - Guitar Mafia.mp3 >>>>>>>>>> To: 3Guitar Mafia.mp3
From: 1-01 Losing all.m4a >>>>>>>>>> To: 1-01Losing All My Friends.m4a
I have the following scenario
A1 - 0.50
A2 -
Once again I return to the brilliant ones on this board. I read the Excel help page for "Split text among columns by using functions". But my parsing task is more advanced than what I could gather from this function.
Here is the contents of cell A1 to be parsed:
Pack type,(make selection),Pack A[=4.95],Pack B[=5.95],Pack C[=7.95]
I need to extract 4.95 into cell A2, 5.95 into A3 and 7.95 into A4. How
Oh also, I have many variations of that example, and want your solution to work for the variations. So here is another actual cell that I have to be parsed:
Qty. discount,(Make Selection),1[=18.95],2 to 4[=17.95],5+[=16.95]
So, each extracted value will always be preceded by = and followed by ]
I have a column C with different text in cells (item's title). Column D - relevant description for each of the items. 100+ rows.
Now, unfortunately, often a spreadsheet with items is updated with many new items. So I get a new spreadsheet with old and new items mixed. I need, somehow, to import descriptions of the old items (Column D of the old spreadsheet) to the new spreadsheet from old spreadsheet. So I want excel to look for old items in column A of the new spreadsheet and, once found, insert a description in the column B from old spreadsheet.
See attachment : Example for forum.xlsx
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
Programming Excel VBA Macro to do OCR (text recognition) from a prt scr screen capture image and input the text into cells. Currently my Excel file has a push-button, and upon clicking on it the macro pastes into Excel the current clipboard image I have created by pressing prt scr while in another program. The macro then crops the image to the region with the applicable text. I have to then manually type the text I see in image format into the appropriate cells.
the VBA coding to automate this? I'd like it to use the clipboard image and run it through OneNote OCR, after which the applicable text values are automatically entered into the cells. Ideally the code will first crop to the region with the desired text before it does OCR. If this is not feasible, it will need to incorporate a method (keyword search?) to hone in on the desired text after the entire prt scr image has been OCRed.
when writing user-define functions in VBA, how do you set up the help on function arguments, so that they appear in the dialog box for the user to enter the correct argument values?
(For example, in the VBA function ACOS, yoh have to enter "Number", and the dialog box explains: "Number is the cosine of the angle you want and must be from -1 to 1." I want to do the same with my own functions).
I am trying to extract values from a text. I used macro to solve the problem. I was able to extract the numbers however i am trying not to extract all of the numbers in text. For example as you can see below, i am trying to get only 22.99 The only unique thing here can be $ sign i believe. I need to put a criteria that selects the number right after $ sign and extracts 5-6 decimals after that.
**work lamp/desk light led;orion8879 final price: $22.99 (store)**
this is the text in a cell and i only need 22.99 not 8879)
I have been working on this worksheet part of which is attached herewith. I would like excel to automatically enter Updated/Inserted in column B against Individuals' names as per the instructions given in column A. For example: As per instruction in A9, B13:B16 should show Updated. I have tried to use the nested if function, but it does not work as I want it to. Also as I am not used to macros or VBA codes, could this problem be solved with formulas?
View 2 Replies View RelatedI have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)
When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.
I need the text in column AQ (the monetary amount) to become red when the the opposite is true.
i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies View RelatedI have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
But Excel does not like the formula at all.
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
I have a spreadsheet where if Cell A2 says "Deposit", Rows A2-E2 would be formatted with a Blue background.
I've tried conditional formatting, but I can't built it into a Macro (I can't make it run), and it only highlights the cell that has that value. I also tried having the spreadsheet filter down to only the values I want highlighted, then highlighting all cells and un-filtering the column- this didn't work either.
It would basically say :
If (any cell in Row A) has text = "Deposit" (it needs to be exact, it can't be "containing"), set cell with the word "Deposit" and 4 cells to the right as Blue (I'm not picky about the color).
I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:
View 2 Replies View RelatedI am trying to enter a value in a cell which is dependent on a corresponding value in a table. I have a table A which has a cab type and a corresponding cab number.
What I want to achieve is that when I enter the cab number in another table say in cell (D3), the cab type is automatically entered in cell (C3).
Table A
Cab Type
Cab No
Indigo
100
Indigo
758
Innova
1544
Indica
1675
Macros question - The user enters a word, e.g. Malaysia in a particular cell (always the same location) and I want the macro to be able to find the next cell with that word in it, but it appears that when using macros you can't paste anything into the find function, so was just wondering what to do!
View 1 Replies View RelatedI am trying to enter a value in a cell which is dependent on a corresponding value in a table. I have a table A which has a cab type and a corresponding cab number. What I want to achieve is that when I enter the cab number in another table say in cell (D3), the cab type is automatically entered in cell (C3).
Table A
Cab Type
Cab No
[Code].....
I have two coloumns on a worksheet. In the first column I have data validation drop down list. I want to be able to: if the first column "Other" is chosen then the next cell will allow text for description, if anything other is chosen in the first drop down list cell then I want the next ( text for descritption ) cell to be resticted.
View 4 Replies View Relatedthis formula =H5*I5*J5/144*G5 in "M5" gives me board feet. I have another cell, "K5", with the species of wood in it using data validation and a species list. How can I format the color of "M5" based the value of "K5"?
View 9 Replies View RelatedI have a VBA that can add a comment based on the cell value, however, I need it to do it for the whole worksheet and not just one cell. I am pretty new to using VBA's, so this is the best code I could find that would perform what I need. If there is a way to have it do run a vlookup, instead of me listing all the cases, that would be great as well.
VB:
Sub AddComment()
Dim strText As String
With Range("s6")
Select Case UCase(.Value)
Case "UPT": strText = "UnPaid Tardy/Late"
[Code] .....
how to rank values in one column based on criteria in another column?
For example: How do I rank Cells in Column A that only contain the text "AL" in column B?
COL...A....B
ROW
1......5....NL
2......6....AL
3......7....AL
4......8....NL
I need to include a prefix of +91 and +911 based on a text in column A. If any cell in column A has a specific text and when I enter a number in corresponding cell in column B it should add a prefix number as below.
If any cell in col A has text "STATE LINE OR MOBILE 1" then the corresponding cell in col B should add a prefix of +91 to whatever number I enter in that cell of col B.
If any cell in col A has text "MOBILE 2" then the corresponding cell in col B should add a prefix of +911 to whatever number I enter in that cell of col B.
If any cell in col A has any other text then the corresponding cell in col B should be the same number as I enter in that cell of col B.
I need a code to delete all rows with "Complete" in Column D below Row 7.
View 2 Replies View RelatedNot sure why this won't work. Just want to insert the text line "11 color" in column "I" if a user enters a number higher than 199 in column "G".
View 3 Replies View RelatedI am looking for some macro code that will hide all rows except for those with certain text (i.e. W-NT or S1-DT). The cell range to search is C22-OU500.
View 5 Replies View RelatedIn short I what to be able to check a check box and have the adjacent cell, which contains the tab name, to then reveal that tab and add a link to the tab to cell contain the tab name. I have done this with copying and pasting existing linked cells, but now I have 100 more to do and I do not want to write repetitive code to get it done.
View 4 Replies View Related