Excel 2007 macro to change headers in work sheet

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.
S
Serena2345Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil RussellTechnical Development LeadCommented:
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

0
Swapnil NirmalManager, Audit AnalyticsCommented:
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)
Loop


End Sub

Open in new window

0
Glenn RayExcel VBA DeveloperCommented:
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).

-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Serena2345Author Commented:
Thanks to all. I compromised and saved the formulas as a macro.
S
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.