Function or Macro to Clean Up Column Headings

[Alex20850]

I have a spreadsheet with several dozen tabs. Each tab is in a format for converting into databases.
I will be using a program to convert them into Oracle SQL insert scripts.
Currently, some of the column headings start with numbers and have spaces, for example 2010 land area.
It should be changed to YR2010_land_area. I am assuming if it starts with a number it is a year.
This is because Oracle SQL columns cannot start with numbers or have spaces.
I am not a VBA programmer, but I have installed function and macro code before, but I don't what would work better in this case.

 

[Alex20850]

I am not going to spend time writing a function until you till me in what way the macro failed. Otherwise the function is likely to fail as well.

Original Column: State Abbreviation
Desired Result: State_Abbreviation
Result from Macro: 0 [zero]

 

[Fluff]

Are you saying it replaced all the headers with 0?

 

[Alex20850]

Yes

 

[Fluff]

Ok, found the problem, try

Sub Alex()
   Dim ws As Worksheet
   
   For Each ws In Worksheets
      ws.Range("1:1").Replace " ", "_", xlPart, , , , False, False
      With ws.Range("A1", ws.Cells(1, Columns.Count).End(xlToLeft))
         .Value = .Worksheet.Evaluate(Replace("if(iserror(left(@)+0),@,""YR""&@)", "@", .Address))
      End With
   Next ws
End Sub

 

[Alex20850]

Sub Alex() Dim ws As Worksheet For Each ws In Worksheets ws.Range("1:1").Replace " ", "_", xlPart, , , , False, False With ws.Range("A1", ws.Cells(1, Columns.Count).End(xlToLeft)) .Value = .Worksheet.Evaluate(Replace("if(iserror(left(@)+0),@,""YR""&@)", "@", .Address)) End With Next ws End Sub

Great, looks good. Thanks!!

 

[Fluff]

Are you happy to use that, or would you still prefer a function?

 

[Alex20850]

If it's not too much trouble, a function would be very helpful.

 

[Fluff]

Ok how about

Function Alex(Cl As Range) As Variant
   Alex = Replace(Cl.Value, " ", "_")
   If IsNumeric(Left(Alex, 1)) Then Alex = "YR" & Alex
End Function

Used like

+Fluff 1.xlsm
	A	B
1	1990 Start Date	End date
2	YR1990_Start_Date	End_date
Main

Cell Formulas
Range	Formula
A2:B2	A2	=Alex(A1)

 

[Alex20850]

Great, I will try tweaking it to handle some more characters, but it is a wonder ful help.

 

[Fluff]

You're welcome & thanks for the feedback.