Skip to Content

AI_FORMAT

Overview

AI_FORMAT uses an AI model to standardize and restructure text based on a specified format. This function is especially useful for cleaning and organizing inconsistent data, making it easier to work with information from various sources. The function leverages the Mistral AI models, which are state-of-the-art large language models for text generation and formatting. For more information, see the Mistral AI documentation.

This example function is provided as-is without any representation of accuracy.

Usage

To use the function in Excel, enter it as a formula in a cell:

=AI_FORMAT(text, format_instruction, [temperature], [max_tokens], [model], [api_key], [api_url])
  • text (str, required): The text or cell reference containing the data to format.
  • format_instruction (str, required): The desired output format description (e.g., “ISO date format”, “formal business letter”).
  • temperature (float, optional, default=0.0): Controls the randomness/creativity of the response (0.0 to 2.0).
  • max_tokens (int, optional, default=1500): Maximum number of tokens for the generated content (5 to 5000).
  • model (str, optional, default=“mistral-small-latest”): The specific AI model ID to use.
  • api_key (str, optional): API key for authentication. Get a free API key from Mistral AI.
  • api_url (str, optional, default=“https://api.mistral.ai/v1/chat/completions”): OpenAI-compatible API endpoint URL.

The function returns the formatted text as a string according to the specified format. If an error occurs, a string error message is returned.

Examples

Example 1: Standardizing Customer Contact Information

Format inconsistent customer data into a standard format.

In Excel:

=AI_FORMAT("John Smith / Marketing Director - Acme Inc / [email protected] - 555.123.4567", "standard business contact card format")

Expected output:

John Smith
Marketing Director
Acme Inc.
Email: [email protected]
Phone: (555) 123-4567

Example 2: Formatting Financial Figures

In Excel:

=AI_FORMAT("Revenue: 2.4m; COGS: 1.1m; Gross Margin: 1.3m (54%); Opex: 950k; EBITDA: 350k", "professional financial statement format with proper currency notation")

Expected output:

Revenue: 2,400,000CostofGoodsSold:2,400,000 Cost of Goods Sold: 1,100,000
Gross Margin: 1,300,000(54OperatingExpenses:1,300,000 (54%) Operating Expenses: 950,000
EBITDA: $350,000

Example 3: Converting Customer Feedback to Structured Format

In Excel:

=AI_FORMAT("Used the product for 3 weeks. Good quality but shipping took forever. Customer service was helpful though. Probably would buy again if they fix delivery issues.", "structured product review with ratings")

Expected output:

Overall Rating: 3.5/5
Product Quality: 4/5 - Good quality product
Shipping & Delivery: 2/5 - Excessive delivery time
Customer Service: 4/5 - Helpful support
Would Purchase Again: Yes, conditionally
Additional Comments: Customer would likely repurchase if delivery issues are resolved.

Example 4: Standardizing Address Data

In Excel:

=AI_FORMAT("123 business park dr suite 12, austin tx 78701", "standard US business address format")

Expected output:

123 Business Park Drive
Suite 12
Austin, TX 78701

Example 5: Converting Notes to Action Item Format

In Excel:

=AI_FORMAT("Tom said we need to finish the report by Friday. Maria will contact the client about budget concerns. Everyone should review the new marketing strategy before next meeting on 5/10.", "action items with responsible parties and deadlines")

Expected output:

ACTION ITEMS:

  1. [Tom] Complete report - Due: Friday
  2. [Maria] Contact client regarding budget concerns - Due: ASAP
  3. [All Team Members] Review new marketing strategy - Due: Before May 10 meeting

Python Code

import requests import json def ai_format(text, format_instruction, temperature=0.0, max_tokens=1500, model="mistral-small-latest", api_key=None, api_url="https://api.mistral.ai/v1/chat/completions"): """ Uses an AI model to format text according to a specific structure or pattern. Args: text: str or list[list[str]], required. The text to format. format_instruction: str, required. Instructions describing the desired format. temperature: float, optional. Controls response creativity (0-2). Default is 0.0. max_tokens: int, optional. Maximum tokens for response generation. Default is 1500. model: str, optional. ID of the model to use. Default is "mistral-small-latest". api_key: str, optional. API key for authentication. api_url: str, optional. OpenAI-compatible URL. Default is "https://api.mistral.ai/v1/chat/completions". Returns: str: The formatted text according to the specified format, or an error message on failure. This example function is provided as-is without any representation of accuracy. """ # Demo fallback: Boardflare endpoint if no api_key or api_url if api_key is None or api_url is None: if "idToken" in globals(): api_key = globals()["idToken"] api_url = "https://llm.boardflare.com" else: return ("Login on the Functions tab for limited demo usage, or sign up for a free Mistral AI account at https://console.mistral.ai/ and add your own api_key.") # Handle 2D list input (flatten to a single string) if isinstance(text, list): if len(text) > 0 and len(text[0]) > 0: text = str(text[0][0]) else: return "Error: Empty input text." # Validate temperature if not isinstance(temperature, (float, int)) or not (0 <= float(temperature) <= 2): return "Error: temperature must be a float between 0 and 2 (inclusive)." # Validate max_tokens if not isinstance(max_tokens, int) or not (5 <= max_tokens <= 5000): return "Error: max_tokens must be an integer between 5 and 5000 (inclusive)." # Construct a specific prompt for formatting format_prompt = f"Format the following text according to this format instruction: {format_instruction}\n\nText to format: {text}" format_prompt += "\n\nReturn ONLY the formatted text. Do not include any explanatory text, just the formatted text." # Prepare the API request payload payload = { "messages": [{"role": "user", "content": format_prompt}], "temperature": float(temperature), "model": model, "max_tokens": max_tokens } headers = { "Authorization": f"Bearer {api_key}", "Content-Type": "application/json", "Accept": "application/json" } try: response = requests.post(api_url, headers=headers, json=payload) if response.status_code == 429: return "You have hit the rate limit for the API. Please try again later." response.raise_for_status() response_data = response.json() content = response_data["choices"][0]["message"]["content"] return content.strip() except Exception as e: return f"Error: {str(e)}"

Live Notebook

Edit this function in a live notebook.

Live Demo

Last updated on