Function or Macro to Clean Up Column Headings

Alex20850

Board Regular
Joined
Mar 9, 2010
Messages
121
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

Board Regular
Joined
Mar 9, 2010
Messages
121
How would I add other characters to be replaced:
What would be the standard for adding different characters>
If I get one, I can add the others.
Change "." to "_"
Change ">" to "GT"
Change "<" to "LT"
Change "=" to "EQ"
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
How many are you going to have in total?
 

Alex20850

Board Regular
Joined
Mar 9, 2010
Messages
121
I don't know. I download spreadsheets for converting to Oracle SQL and there can be all sorts of characters
in the column headings. I don't mind poking around with trial and error if the basics are laid out.

I really appreciate your work.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Function Alex(Cl As Range) As Variant
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array(" ", "_", ".", "_", ">", "GT", "=", "EQ")
   Alex = Cl.Value
   For i = 0 To UBound(Ary) Step 2
      Alex = Replace(Alex, Ary(i), Ary(i + 1))
   Next i
   If IsNumeric(Left(Alex, 1)) Then Alex = "YR" & Alex
End Function
Just add the rest to the array, the order must be the value to replace followed by the replacement.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
My pleasure.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,840
Messages
5,766,729
Members
425,375
Latest member
Bradleyckx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top