Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Unhappy Unanswered: manipulating data in excel

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: manipulating data in excel

    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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •