VBA Partial String Extraction From Variable?
Feb 12, 2013
I have a comment that I'm putting into a variable for the purpose of obtaining the persons name.
Cell C3 (in this example) just contains their employee number, but it's comment contains...
Tech ID: 123456
Name: John Doe
The Tech ID is variable from 3 chars to 6 chars and obviously the name is size is also variable. The end result will be a variable with just the persons first and last name extracted from the comment.
Code:
Dim mycmt As String
Dim techname As String
mycmt = Sheets("tech").Cells(3, "C").Comment.Text
techname = Right(mycmt, BIG-OLE-BALL-OF-CONFUSION)
MsgBox techname
My 2nd question. This is the 3rd time in recent days that I've needed a partial string and Mid, Left, Right, Len, Find just boggles my mind.
View 6 Replies
ADVERTISEMENT
Mar 24, 2009
In cell A1 I have the text
20. Black Angel Blues (Sweet Black Angel) - Nighthawk, Robert
In cell B2 I would like all of the text from the period/full stop after '20' until the - hyphen character
In cell C2 I would like the text after the hypen, but if as in this case there is a surname followed by a first name I would like them rearranged so that the full name is shown first name followed surname without a comma.
The end result in this example would be:
B2
Black Angel Blues (Sweet Black Angel)
C2
Robert Nighthawk
View 9 Replies
View Related
Dec 24, 2011
Examples of content of two cells are:
50.20283625183448da
1195.368480208458hr
where the right alpha characters always count two.
I need the left number as a Double and the right two characters as String (or ?) to compare with Array("mn","da","yr") etc to determine the index I don't know how to write the code.
View 2 Replies
View Related
Jun 16, 2014
I am trying to use the string (sPath), I previously define to preform a saveas function after the script runs.
Code:
Sub CombineSheets()
Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht As Variant
Application.EnableEvents = False
Application.ScreenUpdating = False
[Code] .......
These reports are always located in a subfolder defined as "D:DropboxWorkVerification Reportslocation name" where location name is the name that I want to use to save the workbook.
So as an example an excel workbook report generated in the folder:
"D:DropboxWorkVerification Reports!Test"
Would be saved as "!AirCheck AutoTest Report - !Test.xls"
or a report in the folder:
"D:DropboxWorkVerification ReportsHoliday Inn Surfside"
would be saved as: "!AirCheck AutoTest Report -Holiday Inn Surfside.xls"
Right now the code I guessed on isn't working and its generating an error on compile "Can't assign to read-only property".
View 7 Replies
View Related
Nov 13, 2007
I have a list of 3000 strings, all in seperate cells, in a column.
Within each string, are ( say 13 ) letters ( for convience) "A" through to "M" which are of interest to me
The 'letters' of interest occur either once or twice , or none within the individual strings. When a 'letter' of interest occurs within a string , putting a marker in a column on the same row is sufficient.
Using vba, I have extracted the cases where 2 'letters' exist within the strings, by using 3 nested FOR NEXT loops, with an IF test in the middle of
If (InStr(Cells(i, 2).Value, IndexLetter#1) > 0) and (InStr(Cells(i, 2).Value, IndexLetter#2) > 0)Then
and then put my marker in cells(i,3)
where : IndexLetter#1 is set in the outermost of the FOR NEXT loops as one of the 'letters' of interest, and similarly IndexLetter#2 is set on the next inner of the FOR NEXT loop . The inner most FOR NEXT loop ( i ) is the list of 3000 strings
I want to put a marker next to the string , when only ONE of the 'letters' is present in the string AND NONE OF THE OTHERS
View 9 Replies
View Related
Mar 31, 2014
I am trying to do a sort of index match thing using VBA. What I am attempting to do is to use the prefix of a long number and try to find that exact prefix in a string array, and output that string array value. So this is what I have so far as a test:
[Code].....
So I can match the text exactly so if I put PREFIXB in cell A1 in this example, i will get the msg box saying "YES", but if I make it PREFIXB1231k4j3jj1kj32cj, it will display "NO". I want to get it so that PREFIXB will be displayed in the cell that I put the formula in. So if A1 = "PREFIX1AAA100CF" and cell B1 = "=ABC(A1)", cell B1 will display "PREFIX1AAA".
Now the thing is that these prefixes can have different lengths, but will never encompass the exact prefix of another. So if I had a prefix of: PRE1AB, I won't have a prefix of PRE1A.
View 2 Replies
View Related
Jun 13, 2013
Working with a scheduling report and trying ultimately to get it down to a line item report with the associate information and their earliest start and latest end for the week. Already made a lot of fixes to the formatting involving junking empty cells and trash data, but running into a road block on some of the trash data.
Here is the format of the report at this point. Names have been changed to protect the innocent.
Agent: 2366 Bacon, Kevin
Date
Start
End
[Code].....
So what I need is something that will iterate through the report...such as an while and find the cells in column A that start with "Agent:" It needs to compare that cell to a stored value to see if this "Agent:" is a repeat. If it is then it needs to delete that row and the next one and shift up. If it is not then it overwrites this "Agent:" with the previous one in the stored value and continues the while.
I know what I want to do...but not shure on the code cause I am not great at VBA.
Oh...if it matters probably need to allow for about 10k line items to iterate through...right now report is at 6k...but allow room to grow.
View 6 Replies
View Related
Nov 25, 2008
I have two columns Cust. Phone and State. The Cust. Phone column has the full 10 digit number. Some with the preceeding 1 and some without. The vlookup will sit in the State column. I am trying to perform a lookup using the area code.
I have tried the following but always recieve #N/A.
=IF(LEFT(CELL("contents",A2))="1",VLOOKUP(MID(A2,2,3),D2:E8,2,FALSE),VLOOKUP(LEFT(CELL("contents",A2),3),D2:E8,2,FALSE))
It makes sense if I walk through it but fails to work.
I am trying to use the HTML maker to upload an example but I'm running into problems. I'll update ASAP.
View 9 Replies
View Related
Sep 5, 2007
I need to do a lookup in a column for part of a string and return the adjacent value in a different column. Works fine using VLOOKUP as seen in the following example except that the return value is being truncated.
Lookup range in column A:
A001,A002
A003,A004
A005,A006
I need to find A001 and return the value in column I of the same row. I am currently using {VLOOKUP("A001", LEFT(A1:I500, 4), 9, 0)} This does work entered as an array formula but it is only returning the left 4 characters of the column I value. I need to lookup the left 4 characters in the lookup range but return all the characters in the return range.
View 2 Replies
View Related
Jan 30, 2013
I haven't had the need to work with partial strings till now and having difficulty finding the right context in other threads. I need to put the first 4 characters of one cell into another cell. The line in the below code with the comment is the one I need. It's the only one where I need only part of what is in the cell.
It should be = the first four characters of cells(zRow, "A")
Code:
Dim LastRow As Long
Dim zRow As Long
Dim cRow as Long
LastRow = Sheets("Datasheet").Range("N65536").End(xlUp).Row
zRow = 1
cRow = 2
[Code] ......
View 1 Replies
View Related
Oct 21, 2008
I want to write an macro where it searches for text in a column, but the text may be a partial string. The text is in column B, with account numbers in column A, like this:
Column A Column B
1100 Jay
1101 Jack
1102 Jackson
1103 Jacksony
1104 Jefferson
For example, I want to search on Jack in column B. In the above example, I want it to find accounts 1101, 1102, and 1103, and to copy that information to another spreadsheet, say, sheet2.
If possible, I'd like it to copy the first account number and name, 1101 Jack, and if that's not what the user wants, they click next and they see 102 Jackson and so forth.
View 9 Replies
View Related
Jun 3, 2014
I'm working on a formula to make it enable a part of the text then return the best possible match. Below is my formula
=MATCH("*"&$A11&"*",'[Customer Master List - 05.30.xlsx]Export Worksheet'!$B$82:$B$1298,0)
However, it works with some text but won't work for some.
For example, I have this text CARE-A-LOT, INC and in the master sheet there is a similar text like this CARE-A-LOT. I want it to return CARE-A-LOT as this the best match possible.
View 2 Replies
View Related
Jan 28, 2014
I'm trying to come up with a VBA function that would return cell values based on another cell value.
I can do this by comparing a range (column of cells) with the cell value to match and then return the value of the cell next to it.
For instance,
The function should search a column for partial text, as follows:
TS ID
PDT ID
TS 1.1
PDT 1
TS 1.2
PDT 2
TS 1.3
PDT 3
TS 2.1
PDT 4
TS 2.2
PDT 5
TS 3.1
PDT 6
TS 3.2
PDT 7
In the above table, the function should for partial text and return string as follows:
TS covered
PDTs
TS 1.1, TS 1.2, TS 1.3
PDT 1, PDT 2, PDT 3
TS 2.1, TS 2.2
PDT 4, PDT 5
TS 3.1, TS 3.2
PDT 6, PDT 7
So basically I am searching for partial text TS 1. and so on
View 4 Replies
View Related
Sep 30, 2009
I tend to stumble when trying to pull certain characters from strings, not sure how to stop and start my search.
View 4 Replies
View Related
Dec 10, 2009
I have 2 columns A and B. The data is in column B.
What I'm trying to do is delete entire rows from my current selection if they do not begin with mailto:
View 9 Replies
View Related
Feb 13, 2008
I would like to search cells in column D for the partial string, "PIPE," (A full string may look like this: 'PIPE, 24"ODx0.375"WT API-5LX-65,ERW OR SMLS'). Then, if it's there, return the value "LF" in the corresponding cell in column C. If that string isn't found, then I'd like it to return "EA".
I know this seems pretty easy, but there's a small problem. The word "BENT PIPE," could be in Column D, in which case, I would want it to return "EA" instead of "LF".
View 3 Replies
View Related
Feb 20, 2008
I have created a Userform for entry data's in a Excel2003 file. I would like to retrieve a complete record (= a row) by searching on a string contained in a cell of this searched record-row. Here an example of a row and I'm searching upon "2041" via an inputbox.
idn° claims Dateréf. Suppl supplier Fax n° PO
1AAA2041 2/01/08200039 BOSCH (RAS) 056/20.26.75 774634
View 9 Replies
View Related
Oct 6, 2008
I've got a group of data within a pivot table that I want to be sorted based on only part of the string. For example,
Standard Kitchen
Standard Master Bath
Standard Hall Bath
Optional Kitchen
Optional Basement Bath
First they would be sorted by whether they begin with Standard or Optional. Then within those groups, they would be sorted whether they contain Kitchen anywhere in the string. So that the order would be Standard Kitchens, Standard Anything Else, Optional Kitchens, Optional Anything Else.
View 3 Replies
View Related
Apr 9, 2014
I'm trying to lookup a 10 digit number against a string of numbers seperated by commas. And then return the Carrier Name and On-Time
LOOKUP TABLE
Order #(s) Carrier Name On-Time
5082940535,5082940507 Freight Lines Yes
5083055781,5083056150,5083056098 Ocean X No
Order # Carrier Name On-Time
5082940535 ? ?
5083056150 ? ?
5082940507 ? ?
5083056098 ? ?
View 5 Replies
View Related
Feb 15, 2010
I've posted this query before, not on this forum, but I don't think the replies I've had so far are going to do what I want. Initially I was looking for a formula, but the suggested pile of nested IFs won't work for the number of conditions. I saw a previous post on here for a VBA macro to search for a text value in a cell against the cell contents of a range and it seemed to do at least the first part of what I wanted. I attempted to manipulate it a little to test its applicability for my own nefarious purposes but for the life of me I can't get it working.
This is complicated by the fact that the actual data is commercially confidential, so I can't show you the actual file, but I can fake what I want with two simpler ones. I've attached them to this post. What I want is a fair bit more complex than the other post I found - I want to be able to compare a partial text string from a given cell in a range ('Check Value' in the attached TestBook2 ) against the strings in a range of cells ('Value 1' in TestBook1), and return the corresponding value from 'Test Value' to the corresponding adjacent cell to the tested 'Check Value', with an order of precedence, for example...
Testbook2 contains an entry in C5 of 'a, e, h, z, x, y'. Testbook1 shows that the return for a, b, c, or d is 'moo', for e, f, or g is 'steve' and for g through q is 'fred', all others being no returned value. Moo>steve>fred, so I want the corresponding 'moo, steve. fred or <blank>' cell to contain 'moo'. Conversely, C6 contains 't, u, z' and therefore shouldn't have a value in 'moo, steve, fred or <blank>'. C12 contains 'f, z, s, y, u' and C15 'i, x, z, s', and therefore should display 'steve' and 'fred' respectively.
View 3 Replies
View Related
Jun 30, 2006
I have two questions:
a. Check the code below:
Dim news1 As String, news2 As String
news1 = "new"
news2 = "polygon"
Dim countie As Integer
For buddie = 2 To b1.UsedRange.Rows.Count
'If b1. Cells(buddie, 1).Value Like "new*polygon" Then
If b1.Cells(buddie, 1).Value Like news1*news2 Then
countie = countie + 1
n1.Cells(buddie, 10).Value = "test"
End If
Next buddie
I have 2 strings, news1 and news2, i need to use the like function to check the occurance of these two strings in all the cells, and just for testing purpose, im printing 'test' it out in another excel sheet.
How do i make that work? the commented line shows what exactly i want, only that i dont want to hard code the string values.
b. If i have a value in the cell:
/new/blahblah/anycra/polygon
how do i split it so as to assign two variables to have 'new' and 'polygon'.
View 9 Replies
View Related
Mar 22, 2007
I have a simple lists containing two columns. One column contains a five digit number and the other a vendor name. The vendor name in most cases is two to four words. I am wanting to type in a partial string of the vendor name and it return to me the 5 digit 'vendor' number.
Col A Col B
20567 3M Electrical, Inc.
I want to type in '3m' or '3M' or 'electrical' or 'ELECTRI' and it return the 20567. The other part of this is that there may be two rows with the same info in which I would need to see both...
Col A Col B
20567 3M Electrical, Inc.
21789 3M Tape Division
Is this possible with standard lookup features in Excel or does someone have a VbScript or macro that will accomplish this?
View 9 Replies
View Related
Jun 19, 2014
I have a monthly report. Worksheets for each month, each row is a type of fruit (column A), and the number sold (column B). A new worksheet is added each month with the information. I also have a Summary worksheet - running total of all months: Type of Fruit (column A), Number sold (column B), and Total of all fruits - number sold. I would like a formula for the Summary worksheet - to match that fruit (column A) to any of the other worksheets - matching the fruit (column A), and bring back the number sold (column B). (note some apples are Fuji and some are Honey crisp - those would be totaled together)
ex:
Tab = Jan 14
Fuji apples3
Bananas2
grapes9
TOTAL14
[Code]....
View 2 Replies
View Related
Aug 18, 2014
I have a list of items in Column A and size info on Column B i want to being in the info to another sheet with a vlookup but the problem is that I have a lot of items with different colors so after the item no. it has a dash and a letter or two for the color so i want to bring into my new sheet all info.
I have in my old sheet for all items regardless of color so for instance in my old sheet i will BR1000-EM and then in my new sheet i will have BR1000-R and BR1000-SA and BR1000-YC how do i make a vlookup it should only lookup the values in both sheets only till the dash (i cant use a certain no. like left,6 because the item no can have more then 6 but it always has a dash when it has a color code) also not all items have dashes so the dash is not always there but when its there i would like that the lookup should stop by the dash.
View 6 Replies
View Related
Dec 7, 2008
I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.
View 3 Replies
View Related
Nov 10, 2006
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
View 5 Replies
View Related
Feb 18, 2014
I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.
View 2 Replies
View Related
Mar 15, 2007
Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"
I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.
Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
View 9 Replies
View Related
Oct 30, 2008
I understand that a everything is already considered to be string, but how would I phrase this?
If txtBolow = (a statement which indicates the textbox is full) Then
txtBolow. SetFocus
txtBolow. SelStart = 0
txtBolow. selLenght = Len(txtBolow)
If txLowl = ("") Then
txtLowl. SetFocus
txtLowl. SelStart = 0
txtLowl. selLenght = Len(txtBolow)
What would I use to indicate that the user has completed the data in the textboxes, and the old data is highlighted to make way for the new?
View 9 Replies
View Related
May 20, 2014
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP
View 11 Replies
View Related
