Remove First Part Of String Of Text And Keep The Second Part?
Feb 12, 2014
Is there any way to remove the first part of a string of text in a cell and save the second part?
The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.
The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?
I have a list of data that populates B2:B2900 This data is often prefixed by a 'reference code' that I wish to be removed. Now rater than perform this manually aprox 3000 times is there a formula or some VB code that will complete this for me....
The list of data is shown like:
SC7547-05 - Payne, Freda SC8706-08 - Rungren, Todd SC8714-05 - Travis, Randy SC7517-03 - Beach Boys, The Love Song Now You're Gone SC7512-01 - Horton, Johnny SC8721-15 - Journey
So I wish for the SC7547-05 - to be removed from the first example to just leave Payne, Freda and continue this throughout the list However if the data is found not to include this code (as in the 5th/6th examples above) leave it alone
So if the code of formula is run for the above the outcome would be....
Payne, Freda Rungren, Todd Travis, Randy Beach Boys, The Love Song Now You're Gone Horton, Johnny Journey
I have a simple sentence in cells C3, D3 and E3. The sentence in C3 is: Find Airfare to Chicago, Airline Tickets to Chicago, and More | XXXXXXXXX®
D3 contains: XXXXXXXXX highlights outstanding airfares to Chicago from quality airlines and agencies. E3 contains my list of keywords: Chicago Flights, Cheap Flights to Chicago, Cheap Chicago airline tickets, chicago flight, chicago airfare, chicago airline ticket, chicago airline tickets
The XXXXXXXXX in the C&D cells is the client name which I had to blank out in order to post here. I wasn't sure if you guys would need to know exactly how many characters there are in each cell. In column B3 is the name of the city, in this case the city is Chicago. I have 8000 destinations so I need a formula that will take the city (or any information) in the B column and replace that in the C, D and E columns in the appropriate place. I've attached a small, representative file to demonstrate.
I belong to a fantasy cycling league and after every race, I copy the race results to award each rider their points for the day. The site I visit most frequently lists the riders results in this format:
1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00 2. Lance Armstrong (USA) Team Livestrong etc... 4:06:00
I usually copy the results to my spreadsheet then calculate each riders points.
The'results end up looking like this in Excel:
1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00 2. Lance Armstrong (USA) Team Livestrong 4:06:00
So, the data ends up in 3 cells. The position in one cell, the rider and their nationality and their team in another, and the time of arrival in another cell
Is there a way in Excel where I can get rid of or delete all the data after a rider's name?
I usually end up pasting the rider information to Word, then replacing the (***) with a tab symbol, then pasting back to Excel and get rid of the data that's after the name by deleting the cells to which that data was transferred.
Is there an easier way to do this in Excel, or a macro I can create?...
Is there any way I can Vlookup for just part of a text stiring in a cell. I am trying to vlook if a part number contains the letters “HFR” if it does I want It to refer to the comment I have made in the second column of my vlook table against HFR.
I am looking for a formula to delete information from the middle of a text string. I have ~ 1,000 lines of data. Each text string is composed of the same way. The data I am looking for is two parts of the string (1) all the text up to and including the first “%” character (2) the last six characters.
Raw Data CARDINAL HEALTH 5.5%13CARDINAL 5.513 CARDINAL HLTH 5.5 061513 UST BILL 0%09US TREAS BILL 0%09 UST BILL 0.0 092409 WORLD SAVINGS 4.125%09WORLD 4.125%09 WORLD SVGS 4.125 121509 JP MORGAN 5.375%12JP 5.375%12 JPMORGAN CHS5.375 100112
Desired Result CARDINAL HEALTH 5.5% 061513 UST BILL 0% 092409 WORLD SAVINGS 4.125% 121509 JP MORGAN 5.375% 100112
I am trying to reference a Name of a place from an order number. To illustrate, University Park, IL can have an order # of 6598641373. The only thing is, all I need to reference is the first four digits, 6598. The other worksheet does not have city and state names, they only have the order #s.
I have to sort list of thousands name (3000 names) and remove duplicates. My problem is majority of the names have their title (i.e) Prof. Dr. Ir. Sir. etc typed in, so I need to copy the title in different cell and have their name only. Here's what I expect:
Current List ----> Column A -->Column B Drs. H. A. Andrew Boston, MRE. ----> Drs. H. --> Andrew Boston, MRE Drs. H. Andrew Smith ----> Drs. H. --> Andrew Smith H. Abd. Mohammed Junus ----> H. --> Abd. Mohammed Junus Prof. DR. Jane Doe, MD ----> Prof. D --> Jane Doe, MD Prof. DR. Ir. H. Randy Wong, MBA --->Prof. DR. Ir. H. -> Randy Wong, MBA Dra. H. A. Karen Patel ----> Dra. H. --> Karen Patel Drs. H. A. M. Kangkong ----> Drs. H. A. --> M. Kangkong Prof. Drs. H. A. Kareem Saleh, Ph.D -> Prof. Drs. H. A. ->Kareem Saleh, Ph.D Dra. Hj. Nina Schorder ----> Dra. Hj. --> Nina Schorder Ir. Abdul Jabbar ----> Ir. --> Abdul Jabbar
I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.
Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".
What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.
Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.
I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
building a worksheet to list people, payrates and hours worked etc. the header needs to have fill in areas that change with the job so I entered them in the rows rather than in a proper header. what I want to do is get rid of gridlines in the first 6 rows but leave them intact on the rest of the document. I see templates that this was done somehow.
I need to remove duplicate Part Numbers where other information in the cells will not match. In the following two examples, the only difference is that the COMP_ID: number is different, but for my purposes, the second example is a duplicate and needs to be removed. I have hundreds of rows of this type of information with various part numbers: ...
UserForm1 Current OptionButton names are like ABC123, ABC 124, etc.
How can i change all names, with code - as i am not seeing a find and replace option within UserForms, to all but the "ABC" part, the result of the OptionButton names shall therefor be 123, 124, etc. - delete "ABC" or, find "ABC" and replace with
formula that will pull out the number preceding the * 60 in the text below? In this particular case I would be looking to get 5 as the result. That number could be any number but in the example below it happens to be a 5.
Channel Tune Duration in Seconds} (ID) >= (5 * 60))
What I want to do is sum all the values after the / for a particalur value before the /:
A 8 B 4
I already tried VLOOKUP, but this function wants you to point to a certain cell to return instead of a just a part of the cell. It should return 5 for the first row to be able to perform a sum for every value of A.
How can I perform such a calculation without having to copy the values to separate cells? Can I contain all of this in one function?
Im sure this cant be done because it defies the logic of the LOOKUP function but I know there are some clever people on here so i thought id ask!!
I have 2 workbooks containing similar data from 2 different sources; the one common data item in both is a clients address but have the age old problem of data quality, which needs sorting eventually but i need a quick fix!
In workbook one for example the address will be recorded as RUSKIN RD
In workbook two the address will be recorded as RUSKIN ROAD
A simple look up wont work obviously as the 2 values dont match, then i thought of using the LEFT function but again this goes against the logic of the VLOOKUP.
I need to take out the number in the middle, from the period to the space. The number varies in length so just using the mid function doesn't work. Does anyone know how to modify the mid function to return all chars between the period and the space or is there some other function that may work?
I have 800+ files the problem is that the file name ends in 80 different combination so I need to try all of those for each file.
eg: one of the 800 is "109 st no 103 av" the file could be called: "109 st no 103 av nb1_cleaned.xls" or "109 st no 103 av nb 1_cleaned.xls" or "109 st no 103 av nb 1._cleaned.xls" or "109 st no 103 av sb1_cleaned.xls" . . . .etc
I wrote a code to try all those combinations, the issue lies a space the code adds before _cleaned, how to remove it?.
So the name should be "109 st no 103 av nb1_cleaned.xls" but my code is letting it be 109 st no 103 av nb1 _cleaned.xls
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
If I have a store number (say 1234) and I am trying to search for something which contains 1234 (i.e. the whole string of ABCD1234, ABCD1235, ABCD1236 etc) is it possible to do this in one VLOOKUP formula?
One way of doing it would be to VLOOKUP("ABCD"&"1234",....) but I would rather do it the other way around, to prevent the formula from not working.
I have a conditional formula which gives a value based on what the User specifies.
Example of formula is:
=IF($B$1=0,"CONTACT PERSON - OPTIONAL",IF($B$1=5,"CONTACT PERSON (FINANCE ONLY) - MANDATORY",""))
If the result of the string is " CONTACT PERSON (FINANCE ONLY) - MANDATORY " then - word MANDATORY (not the whole string) should be in RED - words (FINANCE ONLY) should be in BLUE - remaining string should be left in BLANK
I am currently working on creating a dialogue which will allow the user to input a range of worksheets to use for a pivot table.
I want the user to be able to input a variable into the dialogue. For example, I want the input "n-5 - n-1" to mean the 6th from last to the 2nd from last sheets. I am trying to find n - ([0-9]+)? and replace it with Worksheets.Count - $1. In other words, if there are 10 sheets, I want to transform "n-5 - n-1" into "5 - 9".
Here is the code I have so far:
This has been working for strings like "n" or "n-4" but not "4 - n-3" or "n-5 - n-1".
Here is the code I use afterwards to interpret the string once the n's have been replaced: