+ Reply to Thread
Results 1 to 4 of 4

Cleaning up a Column of stings

  1. #1
    Registered User
    Join Date
    08-19-2012
    Location
    Portland Oregon
    MS-Off Ver
    Excel 2007
    Posts
    11

    Cleaning up a Column of stings

    I’d like to find a way to go down column “J” and copy it to column “K”. But if it exist, when copying to column “K”, trim the first “.” in a cell and everything after that. And also, if it exist, trim the first “$” in a cell and everything after that.

    I’d like it to look like this:

    Column J Column K
    Fluid Service…..69.95 Fluid Service
    Air Filter….21.00 Air Filter
    Clutch Fluid Service$69.95 Clutch Fluid Service
    Brake Job Brake Job

    Thank You for your help.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Cleaning up a Column of stings

    Try this formula in B1

    =IF(ISERR(FIND("$",IF(ISERR(FIND(".",A1)),A1,LEFT(A1,FIND(".",A1)-1)))),IF(ISERR(FIND(".",A1)),A1,LEFT(A1,FIND(".",A1)-1)),LEFT(A1,FIND("$",A1)-1))
    Martin

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Cleaning up a Column of stings

    =left(substitute(substitute(j2,".","^"),"$","^")&"^",find("^",substitute(substitute(j2,".","^"),"$","^")&"^")-1)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    08-19-2012
    Location
    Portland Oregon
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Cleaning up a Column of stings

    Sorry, I'm new to VBA and what I'd like to do is to get this code into a macro and run it from there. What would that code look like?

    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1