Extract Alpha From A String And Compare With Another String
Aug 23, 2007
I have a problems here. The problems is attached in the file. I wanna extract alpha/char from a string. Example: I wanna extract the words "(M)" with the bracket from the string "Toothbrush (M)" in column A. After extracting the (M) out, I wanna do a validation to compare the (M) in column B with another data in column C, if the (M) is same as the data called "Medium" in column c, the validation will return "Match" in the column d!
I have another problem with this damn address file. Column H and I have data in them that is often mixed. As shown below, I have used A and B below, but its normaly in Column H and I. I would be greatul if some could write a macro to split the data into the two columns.
Rows 2-8 is what Im presented with. I would like them to look like 11-17
Note that the number in row 8 does not match, so is left for manual intervention....
I can't use the "" sign as delimiter to separate them into different columns because the age,city,name and height fields are in random positions on different cells.The good thing is person's name will always come after "name" string, age is alwals followed by "age" string, so it cannot be like nameheight40Michigan180
I think the following would be the easiest method(not for me tho).If on B1 I had a formula that said "find the string "name" and write anything after it until you reach the next "" character".On C1 field I could have a formula "find the string "age" and write anything after it until you reach the next "" character.On D1 I would have the same for "height" string,then on E1 for city string.
My question is somewhat similar to this one Extract A String Between Two Characters
Formula which outputs the data between 3rd and 4th instances of the "_" character.Can we substitute "3rd and 4th" with a specific strings like "age" or "height" ?
Problem: String is broken into 2 parts within a delimeter. Goal: To keep each String/Segment intact regardless of Alpha/Numeric.
I have a .csv file with 26 segments (commas). This file contains customer data: PO#, Order#, Carton ID, Address 1, Address 2, Address 3, etc.
I've written code to capture all 26 segments as: Input #1, Seg1, Seg2, ... ,Seg26. The variables work fine until it encounter a string with Numeric first then Alpha within the same segment.
For this example we have a string such as (...,187 Main Street, PO Box 369,...) Seg4 = Address Seg5 = PO Box
The Segment Variables in Input #1, get shifted when it encounters the numeric portion of the address...so the result becomes: Segment4 = 187 Segment5 = Main Street Segment 6 = PO Box 369.
So on my next loop when ReadLine is executed...all the variables are shifted and I can't focus on the specific segment that I need to do something to it.
It delimits when it encounters Numeric then Alpha within the string, but accepts the entire string into the variable when its Alpha then Numeric within a delimeter.
How I can keep the entire string in tact within each delimeter?
I have a string of letters in cell column L20 through L3019.
The letters are A, B, C, and R . No other letters.
I wanted to find out if it is possible to remove any duplicates when starting in cell L20 going downward.
For example:
L20 = B L21 = A L22 = A L23 = B L24 = C L25 = C L26 = C L27 = C L28 = B L29 = R L30 = A L31 = C L32 = C L33 = C L34 = R L35 = R L36 = B L37 = B
In the range L20 through L37 all the letters have appeared so my goal is to create a formula where the outcome is BACR. Starting with the first letter B in cell L20
Cell range L20 through L37. Looking for the next letter which would be either A,C, or R. Since we already used the letter B. In this case it's the letter A ABCR
Cell range L20 through L37. Looking for the next letter which is the C or R. In this case it's the letter C CBRA
Finally range L20 through L37. Looking for the letter R which is in cell L29. RACB
I am comparing to columns of data from MS Project using Excel. In one column are the resource names for the project tasks. In addition to the names are the designations for % utilized. So the column's data will look like this:
John Jones[15%],Mary Smith, Fred Arguello[240%].
I want to compare this column with another resource names column which will never have utilization values - only the names.
In order to do that I need to "mask" the non alpha characters out of the first string.
I have a string that follows this format: SAMSUNG CT797F-1
From this string I need to extract 797F only. Further complicating the problem is that its not always SAMSUNG it could be ABB CT797F-1 or KOMSATISU CT797F-1
I have implemented a function that can extract all the numbers, but can't think of how to extract 797F from the various different strings.
I have a list of names in a data set like the examle below. I would like to be able to isolate the last name so I can match the data in this data set with a data set with additional information. The only common set of data I have is the first and last names.
I want to compare a string in a different workbook. and find the relevant cell value of that.
e.x. in one worksheet the value displayed like "DEBIT/ATM CARD TRANSACTION FEE" But in another worksheet the value displayed as "018 - Debit/Atm Card Transaction Fee".
Now I want to compare this string and the value displayed in the column E for this row of workbook 2 should be displayed in workbook 1 in the column D.
I am working on a project where I need to remove records based on certain criteria.
The key pieces of information here are the Company ID, the Contact ID, and the Product Codes. Each Company ID can have multiple Contact IDs, and each Contact ID and have multiple Product Codes. (To be more clear, imagine one company with a Tax department and an Audit department - with each department having a separate contact person. And, each contact person purchasing multiple products.)
I have already summarized the data by identifying the unique Contact IDs and combining the corresponding Product Codes into a string.
Now, I need to determine if any Product Code within the summarized string matches a Product Code in a separate list (a Range of cells). If so, each instance of the Company ID needs to be moved into a different tab. Also, to understand the magnitude of my records, I am working with 25,000+ unique records in the first tab, 8,000+ summarized records in the second tab, and 50+ product codes in the separate range in the third tab.
My first thought was to use a For loop to determine if any cell in the Range is found in each string and, if so, add the related Company ID to a dictionary object. Then, run another For loop against the Company IDs in the summarized tab (8,000+ records) to determine if the ID exists in the dictionary and, if so, moving the row to another tab.
My second thought was using the original data (unique records) - I would load all the Product Codes in the separate Range into one dictionary and run that dictionary against the individual Product Codes in the unique records tab (25,000+ records). If the Product Code in the unique records is found in the dictionary, I would add the related Company ID to another dictionary. Then, I would run a For loop comparing the second dictionary to the Contacts IDs in the summarized tab and move the ones that exist.
In summary, the two ways I have come up with to accomplish what I need includes:
I am trying to to work out how to compare two string variables that I can see with my eyes are clearly a match but getting the right syntax to convince VBA they are the same is my issue.The first example is:
Direct Credit 158824 BRANDON DONNA C/ Lak30
the second example is:
Direct Credit 158824 BRANDON DONNA C/ 30 Lake Vue Parade
but that still would not get a match that I can find code for.
i am chasing the "holy grail" in VBA and I will leave the records that do not exactly match to manual processing although i hate to give up that easily
In the following rows I want to extract the string that ends in K (ie everything after the final space). But I don't know the starting position or how long the string will be.
I have a string (as below - Call them A1:A4) which I would like to seperate into 4 columns (Call them B1:E4).
I have successfully seperated the first part using MID (It's always 5 digits) but the second part has a varing length which then impacts on the third and fourth parts of the string.... Any ideas?
To add to this I am using the POCKET PC version of Excel which does not have all functions so at the moment I am limited to which functions I can use (Can you add functions to the PPC?).
I need to be able extract a string from within a longer string. The information I want will always start 5 chracters in and be bordered by an underscore either side, but could be of any length. For example in the following string, I would want to extract WF602.
I've been looking at the RegExp functions but can envisage problems with this as later parts of the string (WR34) have the same form as the part I need.
I've this question, how do i extract a part of the text using wildcard(example "ID=") in the string in the whole columns and copy to other columns or delete the unwanted.
Below is a example which i create for better understanding.
What I want to do is extract the date only from a string of text in a cell and put it in the cell next to it. The date is not alway in the same position within the text string and although similiar, the text string can differ. There are around 6000 lines in the sheet, I've pasted 4 as an example below.
THE BIG COMPANY LTD 15312178 COLLECTION 14.12.09 The Small Company Ltd 19073864 COLLECTION DATE 17/05/10 511438 511437 511436 ORD LE The Medium Size Company 19080948 COLLECTING WEDS 26/05/10 The Very Good Company 19149554 COLL DATE:28.06.2010 175 PEA PARK ROAD Z7 9AB
I need a formula to extract the numbers from data in a cell:
Examples of data in cells:
G 622 ENTERTAINMENT ( I need to extract 622 ) D 6129 TOYS ( I need to extract 6129) C 1039 Toddler TOYS ( I need to extract 1039 ) R 05 VEHICLES (I need to extract 5 )
All the cells have spaces before an alphabetical identifier, then a space ( it could be more than one in some cases ), then a number ( between 1 and 4 digits ), then a space and a description.
I am trying to extract the ounces (OZ) data from a string: example, BOX 15OZ 1819106287, CONTAINER 12.3OZ 1818176234. I need everything from OZ prior until there is a space.
I have a file which contains one field showing all of the changes to our data. It shows the Field Name followed by a colon: then the Before value followed by a line | then the After value. If multiple fields changed then they are separated by a semicolon;.
I am interest in extracting the Before and After values for changes to the MP.COST field only. Here are examples of the data:
I wanted to return the second number in a string such as this:"0 of 0 jobs ... View all jobs"
The following worked perfectly:=MID(U3,FIND("f", U3)+1,LEN(U3)-FIND("f", U3)-23)
Until this line came up:"1 of 1 job ... View all jobs"
Obviously the "jobs" changed to "job" because of there being only 1, so the "-23" no longer worked. I need this to get the second number out of the string and it can change because the data is being refreshed from a website so it could easily change from one line to another.
I have the following string of text and need to find a way to extract the address and city (separated)
Paste!A2 = 000-0000-0000 12-Mar-13 0.00 12-Mar-13 100.00- J Doe 1111 Excel Rd Vancouver BC 000.00 W Paste!A3 = 000-0000-0000 12-Mar-13 0.00 12-Mar-13 100.00- J Doe 111-2222 Random Ave Calgary AB 000.00 W
The spaces separating the name address and City do change so i have not been successful using a simple Mid/right/left Find formula. Also the street addresses can include a unit # as shown in the text string above. I am honestly at a loss
End results would of course be:
G2 = 1111 Excel Rd G3 = 111-2222 Random Ave H2 = Vancouver BC H3 = Calgary AB
It's for listing items to eBay. Ink Toners to be precise, the macro I have creates everything I need for a listing with a few msg prompts and importing data from another sheet. Saves me hours of work. Each cartridge has a Black, Cyan, Magenta, Yellow. The macro works fine for cartridge sets that go under one name. E.g: Brother TN325 Black, TN325 Cyan, TN325 Yellow... etc.
I've run into an issue with some cartridges which have different names: HP C530a Black, C531a Cyan, C532a Yellow...
I set the macro up to deal with different number, by removing the last character of the "tonername" string "C530", then replacing it with "C531".. etc.
What I haven't factored in is the ones with an A on the end. I need a way to tell it to ignore the a if there is one.
To make it clear, I will be inputting the text: C530a From this, I need to create 3 more strings: C531a, c532a and C533a
Here's the snippet of code I'm using to achieve what I'm already doing.
