Hi I don't know much about creating excel formulas, but I have a need to turn one column of sorted words into alphanumerical versions in the next column over.
Kind of like a word list key code. Each key code must be encompassed around [ brackets ]
Where would I start to try and accomplish something like this?
In Excel I have a field which requires as 14-char alphanumerical value such as AB1D520DGFSF12
I would like Excel to format it as
AB1D5.20DG.FS.F12
How can I achieve this?
I have sheet 1 with a list of numbers in column A and a list of text next to them in column B. I need the code to look at a list of words that will be in column A on sheet 2 and if any of those words appear anywhere within the cell (could be at the beginning, middle or end) in column B on sheet 1 then I need the entire row deleted.
View 8 Replies View Relatedhow would one change the actual code words - instead of using if statements i would like to have when statements that do exactly the same thing, i just want to write "when blah then blah"
View 9 Replies View Relatedi am looking for a code that speaks a word , for example code says "Hello" when the code runs
View 9 Replies View RelatedMany lines on my sheet have the following text in col B.
****** http-equiv="Content-Type" content="text/html; charset=UTF-8"> ****** name="generator" content="http://www.movabletype.org/"> Church Marketing Sucks: Evangelism & Outreach Archives
Is there a way to extract all the text or words between the and tags and put the extracted text into col D?
This code is not accepting the values which is starting from strings like this
for example
bearing 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID
19/128 IN LG, 2-3/64 IN OD, 1/2 IN THK, 5/64 IN ID
steel 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID
19/128 IN LG, 2-3/64 IN OD, 1/2 IN THK, 5/64 IN ID
spares 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID
[Code] .....
I have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.
View 5 Replies View RelatedI'm looking for a macro to remove all words (in a single word per cell format) in a range (approx 100 columns & 7000 rows), except for a list of 100 words.
I'd prefer to email the file if that's okay.
I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:
Table 1 - Before totaling up:
Quantity
Product
5
2 x Button A White
3
4 x Button B Pink
4
5 x Ribbon A Black
2
3 x Thread A White
6
2 x Cloth A Blue
Table 2 - After totaling up:
Quantity
Product
10
Button A White
12
Button B Pink
20
Ribbon A Black
6
Thread A White
12
Cloth A Blue
I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.
I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
On to the topic, I have all the US and Canadian states abbreviated (CO = Colorado, etc.) and was wondering if there is a way to make all of them convert to their respective names in one fell swoop instead of writing it in for each one, one at a time. Something along the lines where I can make, CO = Colorado, TX = Texas and then hit enter and all of the abbreviations would convert. Some kind of command.
View 2 Replies View RelatedI want to sort on number of words, i.e. if a cell contains 1 or 2 words etc, with the cells containing 1 word coming first then cells containing 2 words. Also, if possible, first the cells with fewer characters.
View 9 Replies View RelatedI have tried Sumproduct and Countif and can not get a proper count of rows that contain specific words in multiple columns. I have attached an example spreadsheet where I am looking for a result of 7 rows that contain 1 to 4 specific words within phrases. The example looks for the words: "virus", "spyware", "malware" & "adaware", and ignores any other words like "aware" or "ware".
View 7 Replies View RelatedI have 4-5 separate lists of words/letters and I would like to combine them in a certain order but creating all possible combinations resulting a new word. It's like a conjugation tool for verbs. For example if List 1 = be/el/ki/meg; List 2= m List3=e/é; List4= gy/" "; List 5=" "/ek/sz/nnek So the output has to be (if the order is List1+List2+List3+List4+List5)= bemegy/bemegyek/bemegysz/bemesz/bemész/elmegy/elmennek/ ..... and all possible combinations.
Preferentially I would like to automate it in a way that it straight outputs into MS Access. Also can it be automated that it reads the list from a file (database)?
(Also can it recognize the word by letters? Because the basic word that I would load the program= List2+List3+List4)
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies View RelatedI've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.
View 8 Replies View RelatedI want to be able copy certain things from cells. Is there a way to make excel copy only the name from cells containing this (One name in each cell):
VESNINA E. (RUS)
PAVLYUCHEN. A. (RUS)
KERBER A. (GER)
I basically want to cut out the (...) in all names. I could do it manually, but there could be quite a few players.
I'm having a slight problem with lookup. I have attached the workbook. On the worksheet "Time List", it shows each round what time each team plays in order of time. On worksheet "Ind. Times" it is supposed to list the game times in order of team rather than times.
I'm using lookup, but the values in "Ind Times" are incorrect. If you look at the very first value... It says Monday Night Roast have a BYE Round 1, but looking at "Time List" you see they actually play at 8.00pm. On a side note, if I change the values of the team names to numbers - it works no problem at all.
The Excel function RANK allows one to automatically rank a number within a given range of numbers. Can anyone think of a way to do the same thing with words?
For example, imagine a list of 20 words in a column, with space to add ten more words (complete range B2 to B31, which include the last ten blank cells). If these were numbers, then in the previous column one could simply put in the RANK formula covering the full range, which would then auto update as you added more numbers to the spare ten cells of the range. Assuming that all the values being ranked are unique, and you would therefore get a unique rank number, to order them in rank order, one would simply have the list of possible rank values a column or two removed and VLOOKUP the ranks and their appropriate values.
But what if these numbers were WORDS? The first 20 words are FIXED and will not change, the next ten cells of the range could have any other words typed into them. There is no RANK function for words so how would you suggest that ranking by words is achieved, that does not require any intervention, and as new words were added to the spare ten cells in the range, then the rank would automatically update? This list of ranked words would then be VLOOKUP’d in the same fashion as above with the final list being used to fuel a drop down box, so the user would have an alphabetical listing of all the potential words in the range.
I m running a weekly league with a money list. There are teams that are randomly drawn each week and the winning team gets a certain amount of money. I want to be able to create a money list that pulls a persons names from my list of winners and tallies up there total winnings from all the tourneys combined.
Here is the data I'm working with:
A B C D
1 Tourney #1 Champ SteveEric $100.00
2 Tourney #2 Champ SteveBen $160.00
3 Tourney #3 Champ GregTony $140.00
4 Tourney #4 Champ GregHarry $70.00
I want it to add up the amount of money won by each player. So for example: Ben has won a total of $80. I want it to read that in my money list, then I would like to be able to sort the list using the DATA sort tool. So my main question is, what formula do I put in the cell in order to find the person's name then pull the amount won for that tournament, divide it by 2, then add it up as a total?
Here is what I want the list to look like:
F G
1 MONDAY NIGHT CORNHOLE MONEY LIST
2 NAMEAMOUNT EARNED
3 Steve$130
4 Eric$50
5 Ben$80
6 Greg$105
7 Tony$70
8 Harry$35
We are working on a peer review form using Adobe formscentral (quite handy) It will export to excel which is also handy. The problem is, the questions are not 1-5 but are strongly disagree - neutral - strongly agree. However, we still want to be able to "score" people. How do i make a word like "strongly agree" worth 5 points, so i can get a total and average?
View 5 Replies View RelatedI have a column of Full Names like so:
Tim MacDonald
Pat @ Justine Carey
Penn Fishing C/O Denise Long
Krista Dawkins
Therese le Latshaw
I'm using =LEN(TRIM(C2))-LEN(SUBSTITUTE(C2," ",""))+1 to do a word count. What I need is a formula that will delete all characters between the first and last word if the word count equals more than 2. Even just a formula to determine the location of the first and last space so I can use MID() to cut it out.
I have a spread sheet and it has a column in it with a drop down menu. The words in the menu are Hot, Medium and Cold. I want to have it tell me which one if showing up the most.
View 13 Replies View RelatedFormula to remove the words " Sales Rep"
this is the original word:
Atlanta Sales Rep
I want the formula to remove " Sales Rep"
I'm trying to sum up a range if it contains one or another word. For example, if column A contains the descriptions of products and column B contains the prices, I want to sum all the products that have either of the words "mont" or "blanc" or "starwalker" or all three.
View 9 Replies View RelatedI have sort of a weird request. I've got a chart with data on a patient with a sleep disorder and was hoping to create some charts based on the information I've gathered. For example, I've got a column with the amount of time before she falls asleep and another column with a type of food listed. I want to see if there's any correlation between these two variables. What would be the best type of graph to do this, and what formula would I use?
View 9 Replies View Relatedim currently wonderin if the SUMIF function can have a WHERE syntax?
because i want to add up cells which contain these:
A B
1 VC-ON US 2
2 MC-ON US 5
3 MC-OFF US (STD) 8
4 VC-OFF US (PREM) 9
in another separate cell, i want to retrieve the sum of numbers with the words ON US in column A. so the final result will be 7.
I need VB codes for an activity in excel. Im trying to create an example file for the suppliers to enter keywords (Search keyword) where it should generate different formats of the search (WORDS) and its case sensitive. Suppliers would be typing the keywords in A2 and it should generate the Permutations horizontally against the keyword (B2 C2 D2 E2 etc). Let me know if you need more clarifications.
Example given below and also attached the file for your reference.
New Keyword – Singular Proper Case
Singular-PR
Singular-LC
Singular-UC
[Code]....
I have two columns, one describing the stage of our client (won, lost, postponed .. ) and the other one describing the lead source (referral, repeater, website, ...). Now I would love to have a column chart out of those two values, to know how many website inquiries were lost / won / postponed .. how many referrals were lost / won / postponed .. also.
View 14 Replies View Related