Microsoft Excel - Working with Excel Text-Based Function

Working...

📊 Working with Excel Text-Based Functions

A Practical Guide to LEFT, RIGHT, MID, LEN, SEARCH & CONCAT excel functions

In day-to-day work, Excel is not just about numbers. Very often we deal with text data—emails, IDs, file names, codes, descriptions, etc.

Microsoft Excel provides powerful text-based functions to extract, search, and combine text efficiently.

In this blog, we’ll explore the most commonly used Excel text functions with simple examples and real-life use cases.

🔹 1. LEFT() Function

Purpose: Extracts a specified number of characters from the left side of a text string.

Syntax: LEFT(text, num_chars)

Example:

=LEFT("ORACLEOIC", 6)

Result: ORACLE

Use Case:

Extract country codes

Get prefixes from employee IDs

Read file name initials

🔹 2. RIGHT() Function

Purpose: Extracts characters from the right side of a text string.

Syntax: RIGHT(text, num_chars)

Example:

=RIGHT("INV_2026", 4)

Result: 2026

Use Case:

Extract year from invoice numbers

Get last digits of mobile numbers

Read file extensions

🔹 3. MID() Function

Purpose: Extracts text from the middle of a string.

Syntax: MID(text, start_num, num_chars)

Example:

=MID("EMP-12345-IND", 5, 5)

Result: 12345

Use Case:

Extract employee or order IDs

Parse structured codes

Read values between delimiters

🔹 4. LEN() Function

Purpose:

Returns the total number of characters in a text string (including spaces).

Syntax: LEN(text)

Example:

=LEN("Excel Functions")

Result: 15

Use Case:

Validate text length

Dynamically calculate MID / RIGHT values

Detect extra spaces

🔹 5. SEARCH() Function

Purpose:

Finds the position of a character or word within text (not case-sensitive).

Syntax: SEARCH(find_text, within_text)

Example:

=SEARCH("@", "user.name@gmail.com")

Result: 10

Use Case:

Locate special characters (@, -, _)

Split emails or file names

Dynamic text extraction

🔹 6. CONCAT / CONCATENATE Function

Purpose:

Joins multiple text strings into one.

Syntax (Modern Excel): CONCAT(text1, text2, ...)

Example:

=CONCAT("Oracle", " ", "Integration", " ", "Cloud")

Result:

Oracle Integration Cloud

Use Case:

Combine first & last names

Build dynamic messages

Create file names or IDs

🔹 7. Real-Life Combined Example (Most Important)

🎯 Extract Domain Name from Email ID

Email: john.doe@company.com

Formula:

=MID(A1, SEARCH("@", A1) + 1, LEN(A1))

Result:

company.com

👉 This example shows the real power of Excel, where multiple text functions work together.

✅ Why These Functions Matter

Save manual effort

Avoid data errors

Make formulas dynamic

Essential for reporting, automation & integration work

Whether you’re working in finance, HR, IT, ERP, or integrations, these text functions are absolute must-knows.

OIC - How to Schedule an OIC Integration to Run at 9:15 AM for the First 10 Days of Every Month Using iCal

🔍 Use Case

In Oracle Integration Cloud (OIC), there are scenarios where an integration must run only during a specific part of the month, instead of daily or monthly as a whole.

Example Scenario:

Run a scheduled integration

Execute every day at 9:15 AM

Only for Day 1 to Day 10 of each month

Used for:

Early-month payroll validations

Monthly reports generation

First-10-days billing or reconciliation jobs

The Simple scheduler does not support such complex patterns, so we use iCal expressions.

🛠️ Solution Approach (Using iCal Recurrence)

Oracle OIC supports iCal-based scheduling, which allows precise control over:

Frequency

Monthly - 1 to 10 days

Time - 9:15 AM

✅ iCal Expression to Use

FREQ=MONTHLY;BYMONTHDAY=1,2,3,4,5,6,7,8,9,10;BYHOUR=9;BYMINUTE=15;

🧭 Step-by-Step Configuration in OIC

Go to Integrations >> Schedules >> Open your scheduled integration >> Click Edit Schedule >> Select Define Recurrence → iCal >> Paste the iCal expression:

FREQ=MONTHLY;BYMONTHDAY=1,2,3,4,5,6,7,8,9,10;BYHOUR=9;BYMINUTE=15;

Click Validate Expression

Set:

Start Date (e.g., beginning of the month)

Time Zone (important for correct execution)

Save and Start the Schedule

⏱️ Execution Behavior

Runs daily at 9:15 AM

Executes only from 1st to 10th day

Automatically repeats every month

No manual intervention required

OIC - Automated Report Retrieval Using Twilio SendGrid, Microsoft Graph API, and OCI Functions

Working... will add api details and related info.

Use Case

In many enterprise integrations, reports are not directly exposed via APIs. Instead, systems like Twilio SendGrid generate reports and send secure download links via email. These links are time-bound, redirected, and protected, making manual download inefficient and error-prone.

This use case addresses the need to automatically retrieve a report sent via email, extract the required download link, resolve security redirects, download the file, transform it, and finally deliver it to a target system such as OCI Object Storage or any downstream consumer.

Solution Overview

The solution orchestrates multiple services—Twilio SendGrid, Microsoft Graph API, OCI Functions, and Oracle Integration Cloud (OIC)—to fully automate report extraction and delivery without human intervention.

Solution Steps

Step 1: Trigger SendGrid Report Generation

Invoke the Twilio SendGrid API by passing the required start date and end date.

SendGrid generates the report and sends an email containing the report download information to a configured mailbox.

https://api.sendgrid.com/v3/messages/download?query=(last_event time BETWEEN TIMESTAMP "2025-11-07T16:00:00.0002" AND TIMESTAMP "2025-11-11T23:59:59.9992")

Step 2: Fetch Email Using Microsoft Graph API

Use Microsoft Graph API to read messages from the mailbox.

Filter emails based on:

Sender email address

Subject or timestamp (optional but recommended)

This ensures only the relevant report email is processed.

https://graph.microsoft.com/v1.0/users/{emailUser}/messages

Step 3: Extract Download Link from Email Body

Parse the email body retrieved from Graph API and extract the secure report link embedded in the message content.

Step 4: Resolve Secure Redirect via OCI Function

Invoke an OCI Function, passing the extracted link.

The function handles:

Redirect resolution

Security headers

URL decoding

It returns the final redirected URL required for further processing.

Step 5: Extract UUID from Redirected URL

From the resolved URL, extract the UUID (or unique report identifier).

This UUID is mandatory for subsequent SendGrid API calls.

Step 6: Fetch Final Report Download URL

Call the SendGrid API again using the extracted UUID to retrieve the final report download URL

/messages/download/{download_uuid}

Step 7: Download Report Using No-Security REST Connection

Use a No Security REST Adapter to download the report file directly using the final URL.

This step handles binary file content securely within OIC.

Step 8: Transform Report Data

Apply required transformations based on target system needs:

File format conversion

Data filtering or enrichment

Renaming or metadata adjustments

Step 9: Deliver File to Target System

Send the transformed file to the target system, such as:

OCI Object Storage

SFTP server

Another REST endpoint

From here, the target application can consume the report seamlessly.

Key Benefits

✅ Fully automated, zero manual intervention

✅ Secure handling of email-based report delivery

✅ Scalable and reusable architecture

✅ Ideal for scheduled or event-driven integrations

OIC - How to Run an Oracle Integration Cloud (OIC) Integration on the 3rd Working Day of the Month

Introduction

Many enterprise integrations—especially in payroll, finance, and compliance—must run on a specific working day of the month, such as the 3rd working day.

Oracle Integration Cloud (OIC) schedulers do not natively support “working day” logic, so this requirement must be handled through custom orchestration logic.

This blog explains a reliable and production-ready approach using OIC Scheduler + JavaScript action, with support for weekends and holidays.

Use Case / Business Scenario

An integration must run only on the 3rd working day of every month

Working days exclude:

Saturdays and Sundays

Company or regional holidays

The job should:

Automatically adapt to month start falling on weekends

Not require manual intervention every month

Typical Examples

Payroll file generation

Vendor payment processing

Month-start financial reports

Regulatory data submission

Challenges in OIC

OIC scheduler does not understand working days

iCal or Simple schedules cannot handle:

Weekend exclusion

Holiday calendars

Solution Overview

Design Pattern

Schedule the integration daily and control execution using JavaScript logic

Javascript code used:

function getThirdWorkingDate (currentDate, holidayList) {

// Parse the date

var today;

if (currentDate instanceof Date) {

today = new Date (currentDate);

} else if (typeof currentDate === 'string') {

// Handle different date formats

if (currentDate.includes ('T')) {

today = new Date (currentDate);

} else {

today = new Date (currentDate + 'T00:00:00');

}

} else {

// If no valid date provided, use current date

today = new Date();

}

var year = today.getFullYear();

var month = today.getMonth();

// Start from the first day of the month

var d = new Date (year, month, 1);

// Prepare holiday lookup

var holidays = {};

if (holidayList) {

if (typeof holidayList === 'string') {

var items = holidayList.split(',');

for (var i = 0; i < items.length; i++) {

holidays [items[i].trim()] = true;

} } else if (Array.isArray (holidayList)) {

for (var j = 0; j < holidayList.length; j++) {

holidays [String (holidayList (j))] = true;

}

}

}

var workingDayCount = 0;

// Loop through up to 31 days

for (var k = 0; k < 31; k++) {

var dayOfWeek= d.getDay(); //0=Sunday, 6-Saturday

// Format date as YYYY-MM-DD

var dateStr = d.getFullYear() + "-" +("0" + (d.getMonth() + 1)).slice(-2) + "-" +("0" + d.getDate()).slice(-2);

// Check if it's a working day (not weekend, not holiday)

if (dayOfWeek > 0 && dayOfWeek < 6 && !holidays [dateStr]) {

workingDayCount++;

if (workingDayCount ===3){

return dateStr;

}

}

// Move to next day

d.setDate(d.getDate() + 1);

}

// If no third working day found

return dateStr;

}

console.log(getThirdWorkingDate("2026-02-01","2026-02-03,2026-02-20"))

Key Components:

Daily scheduled integration 

JavaScript action to calculate the 3rd working day 

External holiday list (Lookup / DB / File)

Switch activity to control execution

Solution Steps

Step 1: Schedule the Integration Daily

Use Simple or iCal schedule

Run once every day (early morning preferred)

Step 2: Maintain Holiday Calendar

Store holidays in:

OIC Lookup (recommended)

Database table

Stage file

Pass holiday list to integration as:

YYYY-MM-DD,YYYY-MM-DD

Step 3: Pass Current Date

Use Assign action:

format-dateTime(ora:current-dateTime(), "[Y0001]-[M01]-[D01]")

This ensures: Correct timezone handling and Consistent date format

Step 4: Calculate 3rd Working Day (JavaScript Action)

JavaScript receives:

Current date

Holiday list

Logic:

Start from 1st of the month

Skip weekends

Skip holidays

Identify the 3rd working day

Return the calculated date

Step 5: Control Execution Using Switch

Condition:

currentDate = thirdWorkingDate

True → Execute business logic

False → End integration

Benefits of This Approach

• Fully automated
• Handles weekends and holidays correctly
• No hardcoding of dates
• Reusable across multiple integrations
• Easy to explain in audits and design reviews

Conclusion

Oracle Integration Cloud does not provide a built-in way to schedule jobs on the “Nth working day.”

However, by combining daily scheduling with JavaScript logic, you can achieve a clean, flexible, and enterprise-ready solution.

Best practice:

Let the scheduler run daily and let the integration decide when to execute.

OIC - Minimum Schedule Time in Oracle Integration Cloud (OIC): Simple vs iCal – Use Case and Solution

Introduction

Oracle Integration Cloud (OIC) provides scheduled integrations to execute jobs at fixed intervals. While configuring schedules looks straightforward, many developers face confusion around the minimum time supported for Simple and iCal schedules.

This blog clarifies the official limits, common use cases, and recommended solution patterns.

Minimum Schedule Time Supported in OIC

🔹 Simple Schedule

Minimum supported time: 10 minutes

Configured via UI dropdown options

Intervals less than 10 minutes are not allowed

🔹 iCal Schedule

Minimum supported time: 1 minute

Uses iCal (RFC 5545) expressions

Offers more flexibility than Simple schedule

Key takeaway:

Use Simple Schedule for standard batch jobs

Use iCal Schedule when you need 1-minute granularity

Business Use Case Scenario

An organization needs to: Poll ERP / HCM / Database / FTP. Fetch newly created or updated records. Push data to downstream systems quickly

Expectation

Data should be processed near real time (1–5 minutes)

Challenge

Simple schedule does not support less than 10 minutes. Incorrect assumptions may lead to delayed processing

Solution Approaches

✅ Solution 1: Use iCal Schedule for 1-Minute Polling

Best when polling is mandatory

Steps:

Create a Scheduled Integration

Select iCal-based schedule

Use expression:

Copy code

FREQ=MINUTELY;INTERVAL=1;

Maintain last processed timestamp

Fetch only delta records

✔️ Faster execution

✔️ Supported by OIC

⚠️ Use carefully for high-volume systems

✅ Solution 2: App-Driven Integration (Recommended)

Best practice for real-time needs

Steps:

Create an App-Driven Orchestration

Expose REST endpoint using REST Adapter

Source system triggers OIC instantly

Process and route data to targets

✔️ True real-time

✔️ No polling overhead

✔️ Scalable design

⚠️ Solution 3: Simple Schedule + Smart Design

When real-time is not mandatory

Steps:

Configure Simple schedule (10 minutes)

Use delta logic (timestamp / status flag)

Avoid duplicate processing

✔️ Stable

✔️ Easy to maintain

What to Avoid

❌ Assuming Simple schedule supports 5 minutes

❌ Forcing cron tricks below supported limits

❌ Excessive 1-minute polling without volume control

Conclusion

Understanding OIC scheduler limits helps in choosing the right integration pattern.

Design guidance:

Batch processing → Simple Schedule (10 min)

Near real-time polling → iCal (1 min)

Real-time integration → App-Driven / Events

Choosing the correct approach improves performance, scalability, and maintainability of OIC integrations.

PPT - Creating Professional Integration Architecture Diagrams Using PowerPoint

Creating Professional Integration Architecture Diagrams Using PowerPoint

When Visio Isn’t Available, PowerPoint Becomes Your Best Friend

In many enterprise projects, solution architects and integration developers must document system flows clearly. While tools like Visio or Lucidchart are commonly used, they are not always available in corporate environments due to licensing or security restrictions.

In such situations, Microsoft PowerPoint becomes a powerful and reliable alternative for creating clean, professional architecture and integration diagrams.

This blog demonstrates a real integration use case and shows how PowerPoint can be used to design high-quality diagrams.

Use Case: Source System to Target System File Integration

Business Scenario

A payment file is generated by a Source System and manually uploaded to the OIC SFTP location. Oracle Integration Cloud processes the file and finally uploads it into a Target System for downstream financial processing.

The business requires:

• Clear process visibility
• Batch job tracking
• Error monitoring

A simple but professional architecture diagram

Integration Flow Overview

The integration follows these steps:

• File is generated by the Source System.
• File is manually uploaded to OIC SFTP.
• OIC Scheduler picks the file.
• File is transformed into Target System format.
• File is uploaded to OCI Object Storage.
• Faults are logged in monitoring tools.

Solution: Creating This Diagram Using PowerPoint

Step 1: Create Swimlane Structure

• Use Insert → Table (1 row, 3 columns) to represent:
• Source System
• Oracle Integration Cloud
• Target System
• Format the header row with a blue background and white text.

This instantly creates a swimlane layout similar to professional architecture tools.

From Table design >> take a standard table style.

Step 2: Add Process Blocks

Use Rounded Rectangles for each processing step:

• Scheduler
• Get file from OIC SFTP
• Transform to Target format
• Upload to OCI Object Storage

Use light orange or yellow color for process clarity.

Step 3: Add System Objects

Use distinct colors:

Green → Input File 

Orange → Target System 

Color coding improves readability.

Step 4: Use Connectors (Not Lines)

Always use:

Insert → Shapes → Connector → Right Angle Arrow

This ensures connectors stay attached when shapes move.

Step 5: Add Supporting Notes

Use text boxes for:

“Manually uploaded by business team”

“Upload file to Target System”

These clarify ownership.

Step 6: Add Fault Handling Layer

At the bottom, insert a full-width rectangle:

Fault Handler – Monitoring Tool

This highlights error handling.

Step 7: Align & Distribute

Select shapes → Align → Align Center → Distribute Vertically

This gives a Visio-quality look.

Best Practices

Use consistent colors per layer

Keep uniform shape sizes

Follow swimlane structure

Avoid crossing arrows

Keep text action-oriented

Conclusion

PowerPoint is more than a presentation tool. With the right techniques, it becomes a powerful architecture diagramming solution that works perfectly for integration, data flow, and system design documentation.

This approach is ideal for:

Integration solution design

Technical documentation

Client walkthroughs

Knowledge transfer sessions

Microsoft Excel - working with excel's lookup function

📘 Working with Excel Lookup Functions

🔹 Microsoft Excel VLOOKUP() Function

VLOOKUP always searches in the first (leftmost) column of the table_array

It then returns a value from a column to the right of that first column

Widely used but has limitations (left-to-right only): “Left-to-right only” means VLOOKUP can return values only from columns that are to the right of the lookup column, never from the left.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

📌 Best for simple vertical lookups

🔹 Microsoft Excel HLOOKUP() Function 

Searches for a value in the first row of a table

Returns data from rows below

Less commonly used than VLOOKUP

Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

📌 Best for horizontally structured data

🔹 Microsoft Excel INDEX() Function 

Returns a value from a cell based on row and column number. Very powerful and flexible

Syntax:

=INDEX(array, row_num, [column_num])

📌 Does not perform lookup by itself

🔹 Microsoft Excel MATCH() Function 

Finds the position of a value in a row or column

Often used with INDEX

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

📌 Returns position, not the value

🔹 INDEX() + MATCH() Combined

A powerful alternative to VLOOKUP

Can lookup left, right, up, or down

Example:

=INDEX(B2:B10, MATCH(E1, A2:A10, 0))

📌 More flexible and efficient than VLOOKUP

🔹 Dynamic HLOOKUP() using MATCH() — Advanced

MATCH dynamically identifies the row number

Prevents formula breakage when structure changes

Example:

=HLOOKUP(A1, A1:D10, MATCH("Sales", A1:A10, 0), 0)

📌 Makes HLOOKUP adaptable and robust

✅ Summary Table