Function or Macro to Clean Up Column Headings

Alex20850

Board Regular
Joined
Mar 9, 2010
Messages
146
Office Version
  1. 365
Platform
  1. Windows
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.
 
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"
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
How many are you going to have in total?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top