VBA Function to Separate Arabic Sentences from Numbers

This VBA function will take a string containing Arabic sentences and numbers, separate the Arabic sentences by the / delimiter, and return them while ignoring the numbers.

Function Overview

Purpose

The function SeparateArabicFromNumbers identifies all Arabic sentences in a given string while removing any numbers present. It will return a collection of the Arabic sentences.

Parameters

• inputString As String: The input string containing Arabic sentences and numbers.

Return Type

• Returns a Collection containing the separated Arabic sentences.

Exception Handling

• Handles cases where the input string is empty or contains no Arabic sentences.

Implementation

Option Explicit

' Import necessary libraries
' No external libraries required for this functionality

Function SeparateArabicFromNumbers(inputString As String) As Collection
    ' Create a collection to hold the result
    Dim result As Collection
    Set result = New Collection
    
    ' Validate input
    If Len(inputString) = 0 Then
        MsgBox "Input string cannot be empty.", vbExclamation
        Exit Function
    End If
    
    Dim sentences As Variant
    Dim sentence As Variant
    Dim i As Long
    Dim tempStr As String
    
    ' Split the input string by the '/' delimiter
    sentences = Split(inputString, "/")
    
    ' Loop through each segment and check for Arabic characters
    For i = LBound(sentences) To UBound(sentences)
        tempStr = Trim(sentences(i)) ' Remove leading/trailing spaces
        If ContainsArabic(tempStr) Then
            ' Add Arabic sentence to the result collection
            result.Add tempStr
        End If
    Next i
    
    ' Assign result to the function output
    Set SeparateArabicFromNumbers = result
End Function

' Function to check if a string contains Arabic characters
Function ContainsArabic(s As String) As Boolean
    Dim i As Long
    
    ' Loop through each character in the string
    For i = 1 To Len(s)
        ' Check if character is in the Arabic character range
        If AscW(Mid(s, i, 1)) >= &H0600 And AscW(Mid(s, i, 1)) <= &H06FF Then
            ContainsArabic = True
            Exit Function
        End If
    Next i
    
    ' If no Arabic characters found
    ContainsArabic = False
End Function

Code Explanation

1. Imports: No additional libraries are needed for this functionality.
2. Function Definition: The SeparateArabicFromNumbers function separates sentences based on /.
3. Input Validation: Checks for empty strings and alerts the user.
4. Splitting Sentences: The string is split using Split(), creating an array of segments.
5. Loop and Condition Check: Loops through each segment and uses the helper function ContainsArabic to check for Arabic characters.
6. Collection: Valid Arabic sentences are added to a Collection.
7. Helper Function: ContainsArabic checks if any character in the string falls within the Arabic Unicode range.

Usage Example

To use the function, you would call it from another procedure like so:

Sub TestSeparateArabic()
    Dim sentences As Collection
    Dim inputString As String
    Dim sentence As Variant
    
    inputString = "هذا نص عربي/1234/هذا نص آخر عربي/5678"
    
    ' Get the collection of Arabic sentences
    Set sentences = SeparateArabicFromNumbers(inputString)
    
    ' Output results to the Immediate Window
    For Each sentence In sentences
        Debug.Print sentence
    Next sentence
End Sub

Explanation of Usage Example

• The TestSeparateArabic subroutine demonstrates how to invoke the SeparateArabicFromNumbers function.
• It processes an example string containing Arabic sentences separated by / and numbers, printing the extracted sentences to the Immediate Window.

Conclusion

This implementation offers a straightforward and efficient way to separate Arabic sentences from numbers in a VBA environment. For more enhanced data analysis and coding techniques, consider exploring the Enterprise DNA Platform.