Quote:
Originally posted by johnnie
I am very new to using excel and would like to know how I would change data inside a colomn... basically I have a description colomn that looks like this:
HC 150 AQUACLEAR FILTER
Now HC = Hagan Inc.
I know I can do just a find & replace but in the same colomn I have other items like this:
WB 15" 200WT HC WHISP 60 HEATER
So my problem with F&R is that it will change everything unless I hit find next and replace this one only but I have a DB with over 10,000 products....so that wont be fun... the vendor name is only goingto take up the first 2-4 spaces in each so is there a way to change within the first 4 charaters only?? or possible a better way to do this???
please help!
johnnie
|
Hi Johnnie
Im not sure if this is the best way to go about this but ive written a small piece of code for you to replace the characters at the start of the string.
It will ask you to firstly input what you want to replace and then ask what replacement you would like to make.
If you select the first cell in the column you want then run this then it will search every individual cell in that column until it reaches a blank cell.
To use this module press Alt + F11 to Open the code window
from the menu Bar Choose Insert and then Module
and copy and paste this code in the module that opens.
to run make sure you have the workbook open that contains the Macro
Press Alt + F8 and choose the ReplaceFirstChars option then Run
Quote:
Code
Sub ReplaceFirstChars()
Dim OrigString As String
Dim ReplaceObject As String
Dim Replacement As String
Dim CellReplace As String
'ask user questions to determine what to replace and with what
ReplaceObject = InputBox("What do you want to replace?", "Replace First Chars")
Replacement = InputBox("What do you want to replace with?", "Replace First Chars")
'loop through text and replace what prompted to
Do Until ActiveCell.Formula = ""
OrigString = ActiveCell.Formula
If InStr(1, OrigString, ReplaceObject) = Len(ReplaceObject) Then
CellReplace = Replacement & Mid(OrigString, Len(ReplaceObject))
ActiveCell.Formula = CellReplace
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
|
Hope this Ok
David
dBforums
>
manipulating data in excel
01-21-04, 17:56
manipulating data in excel
Linear Mode
