Finding Date Within String (hyperlink) And Pasting Value
Feb 26, 2008
I have a sheet which contains a folder listing of files with the filename displayed as a hyperlink.
I would like to have a formula which works out a couple of values based on the file name which has been copied into a given cell as a hyper link.
In my sheet Column F contains the file names (as hyperlinks)
e.g. Anab AM1 2006-02-27.pdf
or CD 01_02 2006_05_18(2).pdf
I'd like to create a formula for Column A which will give me the first 2 blocks of text which are seperated by a space (in the examples above it would be "Anab AM1" and "CD 01_02") Anything after the second space should be ommited (including the 2nd space itself)
I'd also like column B to find the date within the filename. Note this is not always the rightmost part of the filename (excluding the .pdf) I've worked out I can use the Find function to find the '2006' (I'll always have the same year in here as my folder listings are sorted by year) but it returns the character where 2006 is located e.g. for "Anab AM1 2006-02-27.pdf" it gives me 10.
My date formats are always in the form yyyy-(or_)mm-(or_)dd
View 9 Replies
ADVERTISEMENT
Jun 25, 2009
I have a range that changes the data constantly, I have to watch that data changing. I am trying to work on a macro that copy that data and paste to another sheet. What would be the code to find the next empty row and paste my data there.
Data is in Sheet1, range A17:E32... and it needs to be pasted in sheet2 starting in F2.
View 4 Replies
View Related
Feb 26, 2014
I have created a list of projects which have hyperlinks. When the hyperlink is clicked, I run an excel macro to display the project details in a pdf. However, I am having a problem knowing which project is selected so I can get the correct project details.
My list is in columns B and C.
Project #
Select Project
9
Project 9
14
Project 14
21
Project 21
I create the hyperlinks here:
For i = 7 To 96
If wshS.Cells(i, 10).Value = "Y" Then
' Copy cell values
wshT.Cells(j, 2).Value = wshS.Cells(i, 1).Value
wshT.Cells(j, 3).Value = wshS.Cells(i, 2).Value
ActiveSheet.Hyperlinks.Add Anchor:=wshT.Cells(j, 3), Address:="", SubAddress:=wshT.Cells(j, 3).Address, TextToDisplay:=wshT.Cells(j, 3).Text
j = j + 1
End If
Next i
When the hyperlink is clicked, I need to find the project # to retrieve the project information. The project number is in the cell to the left of the hyperlink. I want to place that value into PrintFilter.
Currently this line: PrintFilter = wshT.Cells(Target.Row, 2).Value receives the message, error 438 object doesn't support this property or method.
If I place the number in PrintFilter, the macro runs successfully. That is the PrintFilter = 9 below.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim PrintFilter As Integer
Dim wshT As Worksheet
Dim wshTemp As Worksheet
Dim wshTemp2 As Worksheet
[Code] ....
View 7 Replies
View Related
Jul 16, 2014
I need to figure out a way to search for a string in column D and then paste the entire row that string is in to a new sheet. I'd like for this to continue until I paste all of the rows of that string into the next sheet. This is what I got so far, and when I run it, it says an error occurs and then it says either press enter or paste. After I hit enter, it pastes the first row to the next sheet, but that's it. Also should I be doing this with a button?
Code:
Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
[Code]....
View 3 Replies
View Related
Dec 8, 2009
I'm looking for a formula that will count specific word in a cell.
Let say cell a1 has "WLLWWLLLLLLWWLLLWWWLLLWWLLWWLWLWLWLLLWWW"
I want to know how many WWLL are in this cell, which is 4.
I wrote this custom function to provide the answer. It is used like so: ..
View 13 Replies
View Related
Jan 27, 2014
I want to add hyperlinks to certain cells using drag and drop. I've got a userform with a toolbar that I use as a drop zone for files (called tbDrop). In the userform, below the dropzone is a label called "label2"
The dragging and dropping works as a charm, label2 shows me the file location and adds it as a hyperlink to the cell.
However, when I click the hyperlink the file can't be opened.
I've used the result string, added it manually as a hyperlink and recorded it. That worked, but i saw no difference in the recorded macro.
VB:
Private Sub tbDrop_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
Label2.Caption = ""
Dim a As String
[Code]....
View 3 Replies
View Related
Nov 24, 2011
I can't work out how to strip the hyperlink address in cell rRecordID(1,11).
Code:
Private Function GetRecord(lRow As Long)
Dim i As Long
Dim vMyCopy As Variant
[Code].....
View 3 Replies
View Related
Mar 13, 2013
I have this formula on my worksheet to test if column A="-" or "+"
Code:
=IF(OR(A:A="+",A:A="-"),NA(),"")
I would like to enhance this so that the test looks for the the + or - inside column A which might be a whole string rather than just one character and returns the same result (an NA# error if found).
I am doing this so I can then use SpecialCells to clear the cells containing the + or -
View 1 Replies
View Related
Oct 24, 2008
On my spreadsheet I have a column entitled: "Item Title", and the data within this column can look like the following;
View 3 Replies
View Related
Aug 8, 2013
I have list of strings to be searched in column A2 to A150 (A1 has column title). The array that needs to be searched is in B2 to AG1000 (B1 to AG1 has column title). I want each string in column A to be searched in the full array. If match is found the corresponding column title (B1 or C1 and so.) and cell address needs to written to a new result columns in AY and AZ. it should work for duplicates as well i.e. the string can be in all the columns of array from B to AG. and all of them needs to be written to result column.
View 9 Replies
View Related
Jul 31, 2014
I'm trying to extract the number portion from the following string. I'm trying to use FIND to find the first digit between 1 and 2 so I used an "or" argument along with the code number, however it's not working here. What I really want to do is find any digit between 1 and 9 in the string, however I started with this formula for now. why this doesn't work and provide a better formula. I want to understand why this didn't work to understand the functions better. Text string is as follows "standard VAT rate: 20% (Jan 1984)". The formula I used in the following =MID(A253,FIND(OR(Char(49),CHAR(50)),A253),2) where A253 contains the string. Is it possible to use a logical argument within a find?
View 7 Replies
View Related
Nov 17, 2006
there is AT() which finds the first occurrence of a string searching from the left like FIND() and SEARCH(), and RAT() which finds the first occurrence searching from the right-to-left.
A single column with names:
John Doe
Jane Marie Smith
Dr. Alex R. Jones
Bill
I need two columns that would look like:
John.................Doe
Jane Marie........Smith
Dr. Alex R.........Jones
Bill
View 9 Replies
View Related
Aug 11, 2009
I am trying to find numbers from a string. I have for example words "EUR Fwd 9x12" and "Eur Fwd 11x15" And i want to write a function that reads the first number from a string if there is only one number before "x" or two numbers if there are 2 numbers. So I have tried to build following function:
Function NumbersInString(Word As String) As Integer
Dim i As Integer
Dim FirstNumberInString As Integer, SecondNumberInString As Integer
For i = 1 To Len(Word)
If IsNumeric(Mid(Word, i, 1)) Then
FirstNumberInString = Mid(Word, i, 1)
If IsNumeric(Mid(Word, i + 1, 1)) = False Then
Exit Function
Else
SecondNumberInString = Mid(Word, i + 1, 1)
End If
End If
Next
NumbersInString = FirstNumberInString & SecondNumberInString
End Function
View 9 Replies
View Related
Apr 8, 2007
We are trying to make either a macro or a function that will look through the contents of a cell, find the first NUMBER and then paste the results to another column. Below is an example of what may be in cell A1:
#BC7K,03/30/2007,0.00636,0.0069,0.00614,0.0062,0.0,0
We want only to find the FIRST NUMBER in this string, so the result should be 7.
View 5 Replies
View Related
Jul 3, 2014
mockup.xlsxStarting out with a 9 by 11 grid.
Column A will have times that need to be referenced in a lookup. (Ex Sat 8:00am)
Row 1 will have basball field codes reference with a lookup. (Example Field 4
Cells B2:I11 have distinct codes or possibly nothing. (An example would be: Tier 1 A1-A3)
These codes will move around from time to time. They will always remain distinct within the grid, but could appear in any of the cells from B2:I11.
I have 2 columns I want to populate with values from the grid.
First is Fields. I want the first cell to populate with the baseball field found in row 1 of which the code Tier1 A1-A3 falls under.
For example if this season Tier1 A1-A3 is found in the column under Field 4, then I need to return Field 4 as a result.
Second is the time. I want the time to be populated based on what row the Tier1 A1-A3 code falls in.
For example if Tier1 A1-A3 is in the Sat 8:00am row, then I need that value returned.
In short, I need to search the grid and find a given value. I then need to use a lookup (or other tool) to return the first value in that row. And secondly via lookup return the first value in that column.
edit: added spreadsheet example. First sheet is grid in question, 2nd sheet is a typical schedule.
The first Team (Scared Hitless) is A1, the team they play first is Sands Duel Fuel (A3) They play at 11:00am on Field TR3.
You will see on the grid under TR3 and Sat 11:00 the code Tier 4 A1v3. This code is what I want to search and correlate into times on the schedule rather than doing the data entry by hand.
View 1 Replies
View Related
Jun 11, 2014
I have a cell that has approx 22000 characters. I'm trying to remove a specific character string from a cell by doing a find and replace with "". It works for characters in the first part of the cell but not for characters in the last part of the cell.
Example: I do a find/replace for the characters 21242 to "" in column A and I get the expected results. I do a find/replace on 69294 to "", again I get the expected results, but if I do a find/replace on 85203 to "", I get "Excel cannot find what I am searching for" (but its there!)
My cell size is within the max size of 32,767 characters so not sure why its not working.
Attached a sample worksheet.
View 5 Replies
View Related
Aug 3, 2009
I have a string in one of the following formats:
Banana 2, Orange 5, Lemon 0
Banana 7, orange/Lemon 9, cucumber 6
Melon/grape 3, Pineapple/ Orange 1
Banana 1, orangefruit/Lemon 2, pine 8
I would like to take out the first integer that comes after the word Orange (not case sensitive). I'm kinda at a loss here, how do I go about accomplishing this?
View 9 Replies
View Related
May 11, 2010
In a data report i have a column which contains a mix of text and may contain an 8 digit number which could start in 0.
for example
in column A i could have "Hello how are you 01237232 I am fine"
I wish to extract the number into a seperate column, and would ordinarilly use a mid or left/ right function, however the text infront and behind the number will vary in length, which means i cannot do this.
The number will always be 8 digits, could start in 0 but will not always, and it may not be present in all the cells in this column.
How I an achieve this?
View 11 Replies
View Related
Feb 16, 2007
I am trying to search for a string of numbers (column 2) in an array, and have "YES" be written on the same line in column 3 if the string is found in the names ANYWHERE in column 1. Please see the desired results on the picture in column 3.
I have tried many things, including SEARCH function which can only work with 1 cell not many, COUNTIF and more advanced functions, but I think have not succeeded because of my lack of knowledge in arrays.
View 9 Replies
View Related
Oct 28, 2007
I want to find a number within a text string, then count the letters in the same string, then add the number to the number of letters.
For example, if I have 9RR in a cell, I want to add 9 + 2 = 11.
The data will always be arranged with the number on the left side of the string.
So, if the number is a single digit, I know I can use left(A1) + len(A1) -1 to get my desired result. But, the trouble I am having is what if there is a double digit number like 10RR...I'm not quite sure how to create a formula (without using VBA) to give me a result of 12 for this cell.
View 9 Replies
View Related
Apr 10, 2008
I have a mass of text in a cell. I would ideally like to search the text to format in a different font colour so it can be easily seen where the specific text is.
My current way is to use SEARCH in conditional formatting but this changes the whole cell not the specific text I am interested in.
EG. - I interested in the text PETER in my cell so would like PETER to be in red font.
A peck of pickled peppers Peter Piper picked; If Peter Piper picked a peck of pickled peppers, Where's the peck of pickled peppers Peter Piper picked?
View 10 Replies
View Related
May 21, 2008
i need help with 2 formulas to return text within a longer string.
the first result i need is a style #. this will always start at the 5th character, but the length is variable. it will always end right before the last "/" or "" in the string (there may or may not be a / or within the style number)
the 2nd result i need is a description of the style. this will always be after the last "/" or "" in the string and end before a ".", but there may not always be a period in the string.
Text string: ABC123123BASIC TEE.black
Style # result: 123123
Description result: BASIC TEE
Text string: DEF/4567LEAH/FLORAL TANK DRESS
Style # result: 4567LEAH
Description result: FLORAL TANK DRESS
View 10 Replies
View Related
Jun 16, 2012
Title should read: Finding the nth Occurrence of a character within a text string
I have a very long text string that is delimited by about 50 "/" to segment certain values within the text string. I want to be able to extract the text between the 33rd and 34th occurrence of "/". How to do this?
View 5 Replies
View Related
Jan 18, 2013
I've attached an example of the data I am trying to solve this problem for. Basically I am looking for strings of 26 blanks or more in a row, but I want to determine the ending location of the last string of 26+ blanks. I've been able to determine the location of the maximum string of blanks, but that max group of blanks isn't always the last set over 26.
Any link to find the location of last string of blanks of 26 or more? The example I attached is a good example since there are two strings of 26+ blanks with the last one being shorter. I've also include the answer I'm looking at the end of the data in the example.
Example.xlsx
View 2 Replies
View Related
Jul 13, 2011
I am trying to use ADO via VB6 to find a specific input in row A of my excel sheet.
I then want it to return the Column and Row to me separately.
I have a code I already use for returning specific values from cells:
Code:
Function Look(ClosedWorkbookFullName As String, _
SheetName As String, RangeAddress As String) As Variant
Dim conn As Object, rs As Object, SQL As String
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
[Code] ......
Now I want something like this I can use as a function, but it obviously needs tweaks.
View 1 Replies
View Related
Mar 12, 2008
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
View 9 Replies
View Related
Oct 3, 2011
I'm trying to restructure a list of files at work into a format that makes some sort of sense and can be filtered into useful information.
Part of what I need to do, is match up all the drawing numbers that a certain project references. The numbers are formatted like "####A##" ex. 1234A01,1234A02,5678A01, etc. The cells that I need to extract drawing numbers from contain some description or other text (not just the drawing number) so I need to extract the drawing number from that cells value. (Ex. I need to get the drawing number 1234A01 from a cell whose value is "blah blah 1234A01 blah blah blah")
I was thinking I could just search the string for "####A##" but I'm not really sure how....I tried instr, but I think its searching for the literal value of "####A##" rather than treating the # characters as wildcards...
I tried:
Code:
if rngNames.Cells(intx, 2).Value Like ("*####A##*") then
set intStart = instr(1,rngnames.Cells(intx,2).value,"####A##")
'....
end if
but intStart remains 0, so this method is not working...
View 3 Replies
View Related
Feb 3, 2008
I am trying to paste in a decimal equivalent chart, loacted here:
http://www.advancedtool.com/prod02111.asp
which basically has a mixture of whole numbers and fractions, and how they equate in decimals.
When I paste this in, all the fractions turn into dates. So for instance 1/64 turns into Jan/64, and 1/32 is Jan/32. Now, I could go in and change each one, but I would like to know how to do this with cell format on the entire sheet....
I have tried to change the cell formats to "general" or "text" and of course fraction, but in this case, I have to go in and do each fraction cell individually. Now maybe I am being too lazy, and in the time it took me to look up my login information, and type this out, I likely could have re-done the worksheet, each cell individually, but I would like to know the way to fix this automatically.
View 11 Replies
View Related
Oct 20, 2013
I'm trying to keep some basketball stats, and from ESPN the field goals are reported as 10-16, or 5-14, to indicate how many field goals were made out of attempts.
When I paste this into Excel it automatically interprets it as a date. So 10-16 turns into Oct-16, and 5-14 turns into May-14. How do I stop this?
Ignore the screenshot!
View 1 Replies
View Related
Jun 24, 2008
I have been searching the boards long and hard for this solution and have yet to find that applies fully. Every day I need to search through 20 worksheets for transactions occurring on a specific date and copy the row onto a new worksheet. I would like to run a macro that would allow me to search for a specific transaction date in column C of each worksheet and if the date matches it will copy the entire row to a new worksheet.
The issue I am having is that the transaction lists are a running sum of all the transactions for each account. Therefore, the range that the macro needs to search will change daily as well. Furthermore, the date that I would be searching for could potentially appear in column A or B but I only want the row if the date matches that of column C. Also, on some sheets there may not be a transaction at all.
View 9 Replies
View Related
