https://docs.google.com/spreadsheets/d/13a8ssrBIMYph9HztB4CFvJwGBIAnhRULK3fuiyhFDNU/edit?usp=sharing

A. Cleaning Column B (Business_Name) & Column C (Owner_Name)

• The Problem: Extreme inconsistent capitalization (mixed case), and excessive leading, trailing, and double middle spaces.

• Prompt to copy and paste to AI:

I have a spreadsheet containing LGU business registration records.

Column B (Business_Name) and Column C (Owner_Name) contain messy text with random capitalization (e.g. "  espeRanZa, MaRiA cLaRa") and extra blank spaces.

Write a Google Sheets formula to:

1. Remove all leading, trailing, and double middle spaces.

2. Normalize the casing to "Proper" capitalization style (e.g., "Esperanza, Maria Clara").

• Provide the formula for Cell B2 and Cell C2 with a brief explanation.

B. Cleaning Column G (Mobile_Number)

• The Problem: Numbers are formatted inconsistently (e.g., 0917-123-4567, +639171234567, 915-333-4444, 0918.456.7891).

• Prompt to copy and paste to AI:

I have a column of raw LGU mobile phone numbers in Column G starting at Cell G2.

The formats are inconsistent: some have dashes, some have periods, some have +63, and others start with 09.

Provide a Google Sheets formula that strips all non-numeric characters (dashes, periods, spaces, plus signs) and reformats the cell into a standard Philippine mobile layout: "09XX-XXX-XXXX" or "+63 9XX-XXX-XXXX".

C. Standardizing Column H (Application_Date)

• The Problem: Date values are typed in varying formats (e.g., 15/07/2026, July 15 2026, 1990-12-05).

• Prompt to copy and paste to AI:

I have a Column H containing registration application dates.

- Some are formatted as UK style (DD/MM/YYYY).

- Some are formatted as US style (MM/DD/YYYY).

- Some are written out like "July 15 2026".

- Some are ISO style (YYYY-MM-DD).

Give me a Google Sheets formula to standardize all of these messy dates in Cell H2 into a single unified database standard: "YYYY-MM-DD".

🔍 Step 2: AI-Assisted Auditing (Identify Missing Fields & Duplicates)

Once the cleaning rules are established, copy-paste the raw dataset text directly into ChatGPT or Claude and use this prompt to perform an instant database audit.

Copy-Paste Prompt for Data Auditing:

You are a senior database auditor for the Iloilo City Government.

Analyze this raw LGU dataset and output a structured audit report highlighting the following issues:

1. MISSING FIELDS: Find all rows where critical fields (Owner Name, Barangay, Mobile Number, or Capitalization) are completely blank or missing. Provide their Permit_IDs.

2. DUPLICATE REGISTRATIONS: Identify records that have the exact same Business Name or Owner details representing redundant entries. Provide their Permit_IDs.

3. LOGICAL ANOMALIES: Highlight any capitalization values that are formatted strangely or look like extreme outliers.

📈 Step 3: Executive Summary Report Generation

Once cleaned, participants should feed the database to the AI to write a formal presentation-ready report summarizing LGU development metrics.

Copy-Paste Prompt for Executive Summary:

You are a Local Government consultant drafting a strategic brief for the City Mayor of Iloilo.

Analyze this database and write a professional 3-paragraph executive summary detailing:

1. Investment Distribution: Which districts (Jaro, Mandurriao, La Paz, Molo, Arevalo, Lapuz, City Proper) have the highest concentration of business capitalization?

2. Registration Trends: What percentage of applications are currently "Active" versus "Pending"?

3. Administrative Recommendations: Suggest 3 concrete policy steps the LGU can take to speed up "Pending" applications and improve data collection standards.