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 am trying to do is extract the volume size of products in 'ml' from 10k plus products from a description field cell. this description field could also contain the weight of the product in grams so I cannot just do a search for a numeric string , it has to be associated with the milli-litres statement .
is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
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?
Given 5 rows in column A, I have strings that has the word 'Qty' and some none. For any string that has the word 'Qty', I want the numeric value before it. For string that doesn't have the word 'Qty', I want it to be 1. My question is, how should I added the numeric string without a helper column?
Column A Desired Qty to Take
some string name and ended with (4 Qty) 4
some string name without the word Qty 1
In the above example, I would like to have an answer in say cell B1 to be 15
IDMacro Out Put X11891189 cdecde CONFRCONFR 175175 D0101101
I ahve a sheet having two column one is "ID" and other is "Macro Out Put" as shown above , is that possible with VBA code that it give the same result as i shown above in column "Macro Out Put". for reference i also attch the file
I need to be able to locate a date in a spreadsheet bassed uppon a user input date. I am looking for the week that contains the user's date. However it seems even if I initialize the value as Date, it still reads the user's input as a string.
Is there an easy way make sure the system automaticaly recognizes the user's input as a date, or is there a way to quicly convert the string to a date?
I have created a macro that searches for an existing part in my worksheet and copies the found part to the row above. I would like to increment the value of cells B and H in the new copied row. Both cells contain an alphanumeric string that ends with a numeric value. For example cell B has a string like this "APL-DK0030" and cell H has a string like this "Dell Optiplex GX260 V09"....
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 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