I want to be able to create shortcuts for cells by using abbreviations. i.e If I wanted a cell to come up as "Food Services" all I would have to do is type in "FS" rather then the entire word or continually using copy/paste for the various abbreviations. Is this possible through excel or would it be best to be done through ms access?
View 4 RepliesMy workbooks have a column of state names that I need to replace the names with abbreviations. I am doing it manually with the Replace (Ctrl+H) right now, and it's very time-consuming since I'm doing it with numerous, large workbooks. I would be very grateful if there's a quicker solution. The state column is always the same (I). The states are United States and Canada, but I could edit if there's an existing solution from a different country, or modify a simliar work-around.
View 6 Replies View RelatedI am trying to figure out how to not allow a word to be type into a cell. I am relatively new to excel and I am working on a spreadsheet where I do not want the word TOMORROW to be allowed in a cell.
I need to read A1 Rows and if it's values is bold letters then i have to add from top of it's above un bold members like that the should work for 2000 rows Like below Ites income - (A) vlues need to be added from row 3 to row 9
A) ITES
ITES Income
B P O - Domestic
4000101
[Code].....
insert a word object, type... and resize...
Dim objX As OLEObject
Set objX = ActiveSheet.OLEObjects.Add(ClassType:="Word.Document.8", Link:=False, DisplayAsIcon:=False)
objX.Border.Color = RGB(255, 255, 255)
objX.Height = 200
objX.Width = 600
objX.Top = 300
objX.Left = 100
'-------make active
objX.Activate
Set objX = Nothing
i would like to place the object in a worksheet and have all the rows of data move down below the word object (i.e., below the word object--i do this resizing manually now...)...?
I need to convert numbers to text in excel 2007. I have done it before in 2003, but can't seem to remember exactly what I used. I may have used some kind of conditional formatting, but not sure.
Here is an example of what I need.
If I type "7203" in a cell I want "Home Repairs" to appear. I have a list of words associated with a list of numbers and want to be able to type the number in a cell and have the word appear.
I have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code:
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
[Code]....
I was wondering if there was any way to do a cell format so that if I type in a word in sheet1 say "mike" on sheet2 the word "mike" cell would be highlighted? or if there is a formula you could use to do that.
View 14 Replies View RelatedI've inserted the current date into an excel form using the TODAY() function.
I also need to provide the form in French, and when I change the language for the cell with the TODAY() function, it automatically abbreviates the month. For example, today's date appears as "13 nov. 2008" rather than "13 novembre 2008" as I would like it to appear.
Any ideas on how to change this?
I would like to have a UDF to convert any text string to an abbreviation. For example, "Del Puerto Creek at HWY" would be converted to DPCAH. Or "this text string" would be converted to TTS. This has to work on any string of any length in any cell. I guess the UDF would pass each character through a loop and extract the letter immediately after a space and capitalize the letter if it is not already in uppercase. The UDF would have to have the ability to drag using relative references. For example: =AbbrevTxt(A1). It does not matter what the UDF is named.
View 3 Replies View RelatedI have the need to create a workbook that will return values based on the entering of an abbreviation. I will use the chemical elements as an example, I wish to have a column formatted so that if 'Au' is entered the cell populates with 'Gold' or if 'Pb' is entered then the cell is populated with 'Lead'
So in my case I have all the abbreviations in one column and all the full descriptions in another (approx 500 different codes/description)
I have a list of abbreviations in Sheet 2 and Names of Companies (Full name of Company and short name) in Sheet 1.
What I need to do is check if the abbreviation used in the Short Name Field in Sheet 1 is correct based on the given abbreviation list in Sheet 2.
here is the example:
Sheet 1:
Fulll Name of Company
Short name
AMERIPRISE FINANCIAL, INC.
Ameriprise Fin
Sheet 2: Abbreviation List
Full Version
Abbreviation
Academic
Acad
Bank
Bk
Financial
Finl
in the abbreviation list the correct abbreviation for financial is Finl so the short name should have been "Ameriprise Finl" and NOT "Ameriprise Fin". I'd like the rows highlighted in yellow if it is incorrect.
I'm trying to wrtie an IF statement to display one of Q1, Q2, Q3 or Q4 based on the three letter month abbrev. All i have so far is the following, which isn't leading anywhere - and i have a feeling theres an easier way to write it.
=IF(((OR(G2="Jan","Feb","Mar"),)),"Q1",""),IF(OR(G2="Apr","May","Jun"),"Q2","")
I have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document
'or
Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep.
View 7 Replies View Related1. Is there a VBA Function equivalent to the FIND() function, If so What is it?
2. Let's say Im Putting a Date into a inputbox, what is the type # for date (Type:=?)??
Is it possible to export Excel cell contents to Word fields in a protected Word document? For example...
What code would be needed to tell Excel to open up, copy and export the contents of A2 in the active sheet of a workbook to "Field 2" in a Word document named "Report 01" and then put the contents of B2 to "Field 2" etc?
Do both applications have to be opened up at the same time or is Excel able to open up Word on its own? Will the macro be able to....
1. Automatically open up the correct Word document?
2. Look ONLY in a certain folder for the "Report 01" Word document?
or
Bring up a "selection" box that allows you to select the document you wish Excel to export it's data to?
3. Close and save the Word Document without any user intervention?
I need to count how many times the word Test is in the range B4:H9 with
Range N2 = Test the formula below works if Test is only in the cell once.
=COUNTIF($B$4:$H$9,"*" & N2 & "*")
But I have data in cells like below, this is all in one cell, so how would I have it count all the times test is in the range when some cells have test 2 or more times in a single cell?
5
Test
8am-2pm
Test
5pm-10pm
How can I use the IF function, to make entering a word, space then word display correct and incorrect if not.
View 13 Replies View RelatedI need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I started on the code below but am stuck.
Have problems using find and the Dictionary
What Im trying to do is find a certain word in a string then return the number associated with that word
I am trying to automate the below process:
1. I have a excel file with Japanese words in column A and their English equivalents in column B.
2. I am trying to create a Macro and assign it to a button. On pressing the button Macro should be able to:
3. open a form where I can enter location of a word file.
4. Macro should open the word file specified in (3).
5. Macro will pick up first Japanese word from excel file (Sheet 1 - A1)
6. Search for that word in Word file
7. Replace the Japanese words in Word file with their English equivalent from excel (B1)
8. Then it should search for second word (A2) and replace with its equivalent (B2) and so on till it reaches last filled cell in excel file column A.
After a lot of search I could find a code from net (Below), made a few changes, but it is not working.
========================================================
Private Sub OK_Click()
' Requires a reference to Microsoft Word xx.x Object Library
Dim sFile As String
[Code]....
I would like to have a formula find a specific word in column L and return the sum from column E for the same fund from column C.
I tried using =SUMIF(L:L,"*annual*",E:E) but that gives me the sum of annual for the entire column. I need to be able to specify the fund.
word "annual" also appears in "semi annual" so I need to be able to separate the two.
I want to say if the word 'suitcase' is in column W (any row), place the word 'suitcase' in column Z, same row.
View 2 Replies View RelatedI am in the middle of automating a process here at work, the program takes a word, "pencil" for example, from excel. It will then open up a word document with content already in it (premade template). The program will then find all instances of a string, "placepencilhere" for example, and will replace that instance with the string from excel.
Basically I want to be able to take a variable that has a stored string value from excel and use it to replace another variable in a word document.
I tried recording a replace (ctrl+f, replace tab) macro, copying the code, and inserting it into the excel vba code.. but I get a error message. Here is what I have:
---
I have a bunch of cells in column b that have products.
Column B
Dell 24" lcd
vaio sony laptop
8.0 mpxl kodak camera
photoepsonprinter
Basically in Column A I want a formula that'll say.
If the word "dell" is somewhere in cell b1, then put the word "Dell" in cell a1.
If the word "sony" is somewhere in cell b1, then put the word "sony" in cell a1.
And then so on and so forth down through column A. The brand names are potentially endless, so is there a easy way to on like a seperate sheet make a list of brand names and have it pull from there?
Originally I made a if function that said if the brand name was contained in the cell then output the brand name, but that caps at 7 for the amount of brands i can use....
I have found these three different ways to separate a full name in say column A row 1. But when i insert different cells into the equation i get lossed. What is the proper way to separate a collection of full names into first middle and last. Do you highlight the list and put in one general equation or do you have to repeat these steps for each individual name in each cell?
First Name
=LEFT(A1,SEARCH(" ",A1,1)-1)
Middle Name
=IF(ISERROR(SEARCH(" ", RIGHT(A1, LEN(A1) - _SEARCH(" ", A1, 1)), 1)) = TRUE, "", _LEFT(RIGHT(A1,LEN(A1) - SEARCH(" ", A1, 1)), _SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ", _A1, 1)), 1) - 1))
Last Name
=RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-IF(C1="",1,2))
I would like to enable my user to search on any one of about 4 columns, when the result(s) are found I need to return the full row to a result form. I've had no problems getting the information from the form to the worksheet but I've run into a few problems now. I should be able to be able to return the rest of the row with the result if I can determine a way to return the row number. Anyway these are the things that I need (in a nut shell):
1. Return a row number
2. Pass values to a form who should be able to change according to the size of the array
3. Have an array of variable size, with variable headers.
01. I want to Type "Al Raha Pharmacy" in a cell
02. When I type "Al Raha" and I press space, then the word automatically changed to "Al Rahma"
03. I tried this in other cells also but the result is same.
I have weeks in format YY-WW (this week is 09-18) and wish to calculate the full date for the week commencing day for that week. '09-18 is in cell S8. To work out the date I currently have: =DATE(LEFT(S8,2),1,RIGHT(S8,2)*7-8)-WEEKDAY(DATE(LEFT(S8,2),1,3))
Which I thought was working fine, but turns out to be the WC date for 1909. I'll probably have to change the -8, that's not a problem, but I can't work out how to get it to 2009.