Excel 2007 macro to change headers in work sheet

Posted on 2014-08-29
Last Modified: 2014-08-29
I have a reports program that produces header like this
Pay Group - Job Dta      Autolink File Number - Job Dta      Empl ID      Name - Personal Dta      Action - Job Dta      Action Date - Job Dta      Department ID - Job Dta      Location Code - Job Dta      State - Location Info      State - Personal Dta      State Tax Code - Tax Dta      State2 Tax Code - Tax Dta
I need a macro that can go to the header row and delete and text to the right of the  "-" and leave the text to the left.
Any help would be appreciated.
Question by:Serena2345
    LVL 37

    Assisted Solution

    The following code would do what you want for the cell A1, you just need to adapt to work with each of your cells.

    Sub keepleft()
    With ThisWorkbook.Worksheets("Sheet1")
        .Cells(1, 1).Value = Left(.Cells(1, 1).Value, InStr(1, .Cells(1, 1).Value, " - "))
    End With
    End Sub

    Open in new window

    LVL 11

    Assisted Solution

    by:Swapnil Nirmal
    Try below code:

    Sub TrimLeft()
    Dim x as range
    Set x = Range("A1")
    Do until isEmpty (x)
    x.value = Left(x.Value, (InStr(x.Value, "-")) - 1)
    Set x = x.offset(0,1)
    End Sub

    Open in new window

    LVL 27

    Accepted Solution

    That code should work.  If you didn't want a macro, you could insert a new row above row 1 and add this formula to cell A1 and copy across:
    =LEFT(A2,FIND(" -",A2,1)-1)
    (note space before hyphen in quotes)

    Then, copy and replace with values and delete row 2 (your original header row).


    Author Closing Comment

    Thanks to all. I compromised and saved the formulas as a macro.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now