How Like Operator Work With Brackets And Number Tags
Feb 26, 2014How the like operator works with brackets and # tags etc...
View 9 RepliesHow the like operator works with brackets and # tags etc...
View 9 RepliesThis is going to be difficult to explain, but I'll try... I need to be able to format a sheet that has all data in column format. Column A contains a number and B an application. C contains the issue data. I need the issue data to be moved under column A and B as illustrated below. What formula can I do to accomplish this?
Original:
Final:
Can't get html or the image tags to work for some reason.
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?
Is it possible to extract a number in brackets specifically from a cell, then increase it by 15%, and return the result back into the brackets.
For example the cell consists the following:
Mary (78)
so the aim is to get the 78 out for calculation, then change the content of the cell with the new result.
Or is there a macros that can be created for this?
I have attached an excel sheet with the input and output required.
It is basically concatenation vertically with certain tags.
Is there a method by which i can achieve this. MACRO OR FORMULA??
Book_test.xls
I have lots of text with HTML tags attached, I would like to delete the tags and be left with only the text. Is there an easy way to do this?
eg.....
I am building an application through Excel to update specific internal website information. My question is, is there an easier way to identify and view the tags on a web page without having to right-click and "view source"?
View 2 Replies View RelatedI am about to re write my vba project as I can see that I need to make it more robust. One of the main issues I have is that the source XML file is not controlled by me. The quality and content is consistent but sometimes the order of the columns is different. At the moment that totally throws out my macro as it relies on certain columns for certain functions.
Trying to nut out the best way to import an XML file, keep only the specific columns I want - by content not location so then all follow on formula's will work and reference the correct data.
I have one theory that I can't get going. I thought if using defined names based on column headers this would allow me to keep and sort the columns I want. I can't find any examples to support this. I have been looking at the advanced filter but most examples reference exact cells which isn't flexible and leaves open to wrong data being pulled. Since I can't control the source doc what can I do?
Here is the data i'm working with.
Name of player
Score of round
bag tag the player came in with
bag tag the player is leaving with
Here is what I'm trying to accomplish. I usually just copy and paste (then sort the data) but I want a spreadsheet that I can enter raw data and just look at the results I will already have the players names in my sheet. Before the round I'll get everyone's tags it will not always be in order (EX: 1,3,15,22,34,66...etc) some tags will always be missing. After the round I'll enter the scores. What I can't figure out is how to leave my sheet in alphabetical order and have my spreadsheet populate the "leaving tag" column with the correct tag based on the players score....
name score tagin tagout
bob 22 2 2
carl 44 15 23
ed 33 23 15
Can't tell if that translated, couldn't use a table....
basically I would like to leave column A where it is the whole time add the data to column B and C The use the data from column B to sort the values from column C numerically to column D (basically copy and rank then display the numbers in order). To throw in a twist if the data in column B is the same for two or more people the formula would need to take the lower number from column C to determine who gets the lower tag in column D.
having to create some kind of database in access....
I have a pie chart, and the value's are based off of a refreshable query. How can I set the pie chart tags to, for example, =C14 and then the next tag =C15?
I know you can go to source data, and select the cells that you want the pie chart to display, but the problem with that, is when the data is refreshed the data in the cells refresh, whereas the pie chart tags do no.
I have on occaision seen -- within a formula. e.g =SUMPRODUCT(--(PMonth=$A3),--(PName=$A$1),PLTime). Could someone explain the relavance/effect or use of this as it seems outside of standard operators.
View 9 Replies View RelatedHow to use the LIKE operator? I have tried:
mytable.field like '098'
mytable.field like '098'
mytable.field like '&098&'
mytable.field like '*098*'
This is in A SQL string connecting to a MSSQL db over ODBC
Excel has operators for Less than, Greater than, Equal to, etc., etc. Does anyone know if there is a Between operator? (Maybe an undocumented or little-known feature like DATEDIF)?
There is a Between Feature in the Conditional Formatting Dialog Box - it would sure be nice to be able to use it in a non-formatting formula, or maybe you could explain how you pros achieve a "between" result without resorting to VBA.
Finding a way to tell excel to pull Comments and Tags from files. I pulled the following code from another source:
VB:
'Force the explicit delcaration of variables
Option Explicit
Sub ListFiles()
'Declare the variables
Dim objFSO As Scripting.FileSystemObject
Dim objTopFolder As Scripting.Folder
[Code] ....
I have been using it to get the Name, Size, Type, and relevant Dates associated with files in a folder. However, this doesn't seem to work with finding Comments, Tags, etc.
I have found functions that are used to pull Comments and Tags from files but haven't been able to incorporate them in to the code I already have.
I'm trying to figure out a macro that will add b and /b tags around bold text. Bolded text can appear multiple times within a cell. I need it to run on all cells within a sheet.
So I need it to:
1. Search for bold strings within cells
2. Identify the length of the bolded strings
3. Add b and /b at the beginning and end of those strings
4. Repeat for all active cells
The only macro I was able to work out adds tags to only the last bolded string, whereas I need the tags on several non-continuous strings.
I have multiple excel sheets wherein the content has bold, italic, underlined, normal text. I want to replace such texts with relevant html tags.Example is below
Identify the adjective in the following sentence. Can you please pass me the black pepper?
should change to
Identify the <b>adjective</b> in the following sentence. <i>Can you please pass me the black pepper?</i>
Any way via VBA to remove HTML tags from a string that has been imported into a cell?
For example, if the cell contained(bearing in mind I can't actually use HTML tags in this post...
"LeftPointyBracket" B "RightPointyBracket" CatDog "LeftPointyBracket" I "RightPointyBracket" Elephant "LeftPointyBracket" B/ "RightPointyBracket"I would need CatDogElephant returning. So, any pointy brackets and anything between pairs of pointy brackets needs to be removed.
VBA method to remove HMML tags from the contents of a cell?
It needs to remove all occurrences of the "" characters, plus anything that is found between a "< >" pair.
I am trying to add a number to a cell for work breaks. Below is what I am trying to do:
If F3 is < 4 then 0
If F3 is > 4 but < 6 then .25
If F3 is < 6 then .50
I have a spread sheet I have set up to calculate sales results, I have a column for each week in the quarter with the date at the top and a different page for each individual. I want to be able to enter a number to show how many weeks we have had and the spreadsheet to give me a cumulitive amount for the cells up to that date.
For example if I was to enter 1 in a cell A1 I would want the sum t work out just C3 for example.
If I enter 3 in A1 I would want the sum to be C3+C4+C5 wich is the three cells.
If I enter 6 in A1 it would be C3+C4+C5+C6+C7+C8 etc.
i want to put a number of graphs on to a work sheet to pringt them out to make it look good. How can i using a macro fix the size of that graph and the location of that graph on the worksheet
View 11 Replies View RelatedI am using Excel to produce work orders and I need each one to have its own unique number. Much like an invoice or contract number, each time I open the file I would like Excel to generate a new number for that spreadsheet with a value of 1 more than the previous spreadsheet.
View 14 Replies View RelatedMy problem is that my VLOOKUP formula will not return any data when it doesn't like the format of the data it's looking up.
Example: I have a spreadsheet that displays revenues earned by assets.Every month I export a table of data from an accounting software program with (a) asset numbers, (b) invoice date, and (c) monthly revenues.Then I copy the data into Tab 2 of my spreadsheet.On Tab 1 of the spreadsheet there is a table that lists Assets 100 through 120. Column A has all the asset numbers.Each month it varies as to which assets earned revenue and which one's did not. Usually between 10 and 15 assets earn revenue in any given month and about 5 do not earn revenue.On Tab 1 there is a column with VLOOKUP formulas that looks up the asset number in column A of Tab 1 and points to Tab 2 where the data that was exported from the accounting software program is located.Let's say that in July 2012 that Asset 1001 earned $35,000.On certain months, the VLOOKUP formula looks over to tab 2 and "returns" the $35,000 revenue with no problem.On other months, it will not return anything, apparently it does not like the formatting and does not "recognize" the asset number.
revised this code to reflect the "not equal to". I want the user to only enter "In" or "Out." Therefore, If cells b19:B26 does not have either word, then the message box will prompt the user to enter the correct word again. I think my code needs to be revised into some kind of loop.
View 9 Replies View RelatedMOD Operator gives one result when this is used as worksheet function and the different one when I use that in VBA.
Cell A1 = 01-Jan-08
Cell A2 = 02-Jan-08
In Worksheet
=MOD(A1-A2,8) Gives 7
In VBA
Reminder = Range("A1")-Range("A2") Mod 8 Gives -1
Basically I have a large database of folders of examples of projects/notes etc, each with essentially lots of potential topic "tags". I want to sort them to be able to tell the macro what topic I am looking for and it give me the folders that contain that info. I will need to tag each folder individually (which is no problem). I am looking for a piece of script (that either exists or that I can write) that will do the following:
- tag specific folders with numerous "tags". The folders will all be stored in a database. The tags will be user input.
- sort these folders based on specified tags and show filepath/location/name/file number or something unique based on the tags input.
I'm trying to take the contents of an excel speadsheet and write it to an html file with my own formatting. For example I would like the row:
|32|55|28|
to end up in the html file as:
<tr>
<td class="align-right">32</td>
<td>55</td>
<td class="">28</td>
</tr>
In theory it seemed fairly simple to loop through a sheet and write before and after each cell, but once I tried writing the code I realized I was in way over my head.
I would like to find out a way to work out the number of minutes worked during particular shifts for weekdays. Basically I have two columns, one for start time, and one for end time. They are formatted like dd/mm/yyyy hh:mm. So they have the date in there as well.
I would like a formula that would look at a range say A1-A11 and work out what shift it is and then output number of hours worked per shift. Day shift would start at 8am and finish 5pm, Twilight shift would start at 5pm and finish at 9:30pm, night shift would start at 9:30pm and finish at 8am the next day. So I would need it to check for example the start and end times (and dates) and then output 3 rows that show the total minutes worked.
There will be multiple days so it would need to say for example Monday Day, Twi, Night, Tuesday Day, Twi, Nights etc. Up to Friday Day shift because we don't work Friday Twilight or Nights, and we don't work Weekends.
Basically there is a list of jobs completed with Start Time and End Time for each and I also have a column that works out the number of minutes worked on that job. So the formula would need to look at many rows.
I'm using the below code to get only those rows which contains 'Deploy' or 'Pre-prod' but it gives less rows than its supposed to do.
View 11 Replies View RelatedWhile proof-reading my code, I've noticed that these two true select cases ("S" and "W") shown below wouldn't "yield" what I want. I've tried to replace AND (highlighted in red) with "," (comma) but I still get the same result - it still returns a value evaluating either one of two conditions but not BOTH:
Code:
sChar = Split(Waypoint, ",")
For j = LBound(sChar) To UBound(sChar) Step 3
Lat = sChar(j)
Lon = sChar(j + 1)
Elev = Val(sChar(j + 2))
Next j
[code].....