[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Excel 2007 macro to change headers in work sheet

Posted on 2014-08-29
Medium Priority
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

by:Neil Russell
Neil Russell earned 668 total points
ID: 40293117
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
Swapnil Nirmal earned 664 total points
ID: 40293383
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

Glenn Ray earned 668 total points
ID: 40293388
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

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

873 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