Word Substitution

Nov 25, 2009

I have a list of abbreviated words in product descriptions (about 6,000 items), need to replace with proper words. I am now doing this with Edit, Find, Replace and I check the list manually with my eyes in order to avoid replacing wrong words. It is very, very time consuming (I've already spent 2 days on this job).

Just wonder if there is any way to do this faster and more accurate.

View 6 Replies


ADVERTISEMENT

Multi Substitution -- No VBA

Jul 24, 2008

What is the best method for searching and replacing within a string when the string to be replaced could be say 1 of 10 options and could appear in any position within the string ? To make it easier let's say wherever those sub strings appear they are to be replaced (ie none to be left behind), and lets say they are all to be replaced by another character -- specific to each string being replaced.

Example:

Say I want to replace any digits in a string, to be replaced like for like as follows:

0 -> a
1 -> b
2 -> c
3 -> d
4 -> e
5 -> f
6 -> g
7 -> h
8 -> i
9 -> j

Sample strings:

Luke is 0 Donkey
Luke is 0 Mul4
0nd Luk4 is not very cl4v4r
Luke can not h40r very w4ll.

etc...

View 9 Replies View Related

Cell Replacement (Substitution)

Feb 17, 2008

I have a cell with a url in it, Say in A1 I have the following url:
mrexcel.com/forum/forumdisplay.php?f=10

I want Cell B1 To copy what's in cell A1 but delete everthing after the backslash"/" In B1 want it to show: mrexcel.com

What I have now doesn't work, which is:
SUBSTITUTE(A1,"/*","")

An attempt to use a wildcard (*) to remove everything after the backslash. Doesn't work
What's the correct formula that will allow me to do this automatically?

View 9 Replies View Related

Adding Additional Characters For Substitution

Sep 20, 2008

i have a macro that copies info from cell in row A and uses that info to name the whole row, now if the name has a space between the words it uses _ (underscore) substition so i don't get an error (when row name is defined - you can't use any special characters or spaces) i was wondering if anyone can help me to add "-" besides the space

here is the macro
Sub Macro1()
Dim a As Long
For a = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(a, 100) = Application.WorksheetFunction.Substitute(Cells(a, 1), " ", "_")
ActiveWorkbook.Names.Add Name:=Cells(a, 100), RefersToR1C1:="=Sheet1!R" & a
Next a
End Sub

View 9 Replies View Related

CSV Import: Dynamic File Name Substitution

Nov 20, 2006

I'm trying to get a user friendly button to simply select then import a csv file into excel. I recorded a macro which runs on clicking a command button. below is the

Sub csv1()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:Documents and SettingsBatDesktopCSV_53415_20061120_091752.txt" _
, Destination:= Range("A4"))
.Name = "CSV_53415_20061120_091752"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437..............................

View 3 Replies View Related

Substitution Of Specific Characters In Alphanumeric String

Jun 13, 2013

I have a large column of text with multiple entries similar to this: PC3L-10600R-9-11-A0. I need to replace the "11" with a 13. However I have other instances where the 11 appears (PC3-12800E-11-11-D0 or PC3L-12800R-11-11-A0).

I have found that I can use SUBSTITUTION

{=SUBSTITUTE(A50,H50,I50,1) and =SUBSTITUTE(A61,H61,I61,2)}

to handle the specific instances but I'd like to have a way to combine this type of function logically in one command.

View 9 Replies View Related

Opening Word & Count Word Instances In A Word Document

May 26, 2006

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.

View 2 Replies View Related

Export Cell Contents To Word Fields In A Protected Word Document

Jul 6, 2009

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?

View 2 Replies View Related

Excel 2003 :: Count How Many Times A Word Is In A Range / Word Can Be In Cell More Than Once

Feb 16, 2012

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

View 5 Replies View Related

Using The IF Function, To Make Entering A Word, Space Then Word Correct

May 6, 2009

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 Related

How To Search For A Word In A Column And Have Entire Row Opaque Once Word Is Found

Sep 3, 2009

I 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.

View 14 Replies View Related

Find A Certain Word In A String Then Return The Number Associated With That Word

Jun 9, 2009

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

View 7 Replies View Related

Pick Word From Excel Search In MS Word And Replace All

Jan 4, 2012

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]....

View 1 Replies View Related

How To Find Specific Word And Sum The Column According To That Word

Mar 14, 2014

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.

View 5 Replies View Related

Searching For Word Copy Word To Different Column

Jan 22, 2013

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 Related

Replace Word In MS Word With Varable From MS Excel

May 1, 2009

I 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:

---

View 11 Replies View Related

If Word Is Contained Within A Cell, Ouput That Word In Another

Jul 18, 2008

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....

View 9 Replies View Related

Word Typed Is Replaced With Another Word?

Jan 22, 2014

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.

View 1 Replies View Related

Word 2 To Be Bumped Over To The Next Column And Word 3 To The Column After That

Dec 7, 2009

I've imported a lot of data and it looks like this:

blahblahblahWord 1 Word 2 Word 3

First, I want to erase the "blahblahblah"

Second, I want Word 2 to be bumped over to the next column and Word 3 to the column after that.

Is there a formula to do this? There's so many rows of data that it will take a long time to do it manually.

View 9 Replies View Related

How To Take The First Word / Name

Aug 26, 2007

How to get the first name or word from "Jake Madrigal Cortez"...

View 9 Replies View Related

Give A Word A Value?

Mar 31, 2009

Im trying to figure out to give a certain word a numeric valuve in excel?. Lets say that the word "car" has the valuve of 10, and the word bus has a value of 20. Have found one possible way to get around being forced to give a certain word a value by doing alittle macro, but havent got it to work yet (and im not sure if its even possible to get it to work), I have a post in the macro section about it.

View 3 Replies View Related

How To Word Value Transfer

Jul 11, 2013

I have an excel sheet that contains different values in data cells for example: A10, A22, A45 etc.

I want to have a button which when clicked, should create a word document from a template and the values at these cells should go into the template file. Thats it.

So say when the button is clicked, the newly created document should be something like this:

The award goes to Mr. Value in cell A10 living at Value at cell A22 of amount Value at A45

View 8 Replies View Related

Select From One Word To Another?

May 13, 2014

My current code is below and works, but I need to change it some. I dont want to select for one value to another, I need to select all "YDF" in column E. (They will all be right after each other)

HTML Code: 

Sub Selectword()
Dim rng As Range
Set rng = Range("E1:E" & Cells(Rows.Count, 5).End(xlUp).Row)
With ActiveSheet
.Range(rng.Find("YDF"), rng.Find("YEG")).Select
End With
End Sub

View 1 Replies View Related

Count Same Word

Apr 27, 2009

how do i find the number of times the same word is used in cells from C3 to
I16

View 4 Replies View Related

Excel To Word

Dec 1, 2009

i have a set of data in excel.. i want that, when i click a command button on an active worksheet, it will be transferred in ms word all the data found in excel.

View 14 Replies View Related

Get All The Characters In Between The Word EA

Dec 16, 2009

cell A1 contains:

001001 28 EA 12 CATALOG #: 9358171

I would like to get all the characters in between the word EA and CATALOG and put them in B1 and so on....

View 2 Replies View Related

Extracting The First Word

Aug 8, 2007

How do I extract the first word of a cell contents?

For example, cell A1 has "John Smith"
I want cell B1 just to show "John"

Obviously, the first word would be of varying lengths, so I'm guessing the formula would need to find the space.

View 14 Replies View Related

Delete Row Containing A Word

Sep 24, 2013

I have a Sheet1 and I need to delete the entire row(s) that contain the word "BUYER" located in column A using VBA.

View 5 Replies View Related

How Numbers From A Word

May 9, 2008

Well it look pretty simple but is it possible.

What I have is a list of code.

KLJBC1234
KLMS234
KLVS12344
KLJBC235

All the numbers at the end is unique. So what was planning to so is just take the numbers out. As you can see is range from 3 to 5 number. Is there a formula to just pull the numbers out? max

View 9 Replies View Related

Extract 2nd To Last Word

Jul 18, 2008

How do I extract the second to last word from a string of text in one cell and put it another.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved