Hi all, I have some text in a table in Word, which I want to get from this format:
Domain1Fruits and Vegetables of the Day
Sub-domain1A5Types of Fruits and Vegetables
Task Statement1Eating fruits that determine whether you will be healthier.
To this format:
Domain: 1 - Fruits and Vegetables of the Day
Sub-domain: 1A5 - Types of Fruits and Vegetables
Task Statement: 1 - Eating fruits that determine whether you will be healthier.
Syntax is effectively:
Domain: # - DomainDesc
Sub-domain: #XY - SubdomainDesc
Task Statement: F - TaskStatementDesc
Where '#' is going to be a Single-digit Number
Where 'X' is going to be a Single Letter
Where 'Y' is going to be an Integer/Number
Where 'F" is going to be an Integer/Number
Where 'DomainDesc' is going to be a sentence describing the Domain
Where 'SubdomainDesc' is going to be a sentence describing the Sub-domain
Where 'TaskStatementDesc' is going to be a sentence describing the Task Statement
Effectively I have a 4x900 table in Microsoft Word, and the example text above is all in one column (i.e. so there's lots of similar examples in the same format above), but it's not formatted nicely (as per above), and I need it to look like the 'To' format above. I don't want to have to do this formatting manually for all 900 rows in the column so thought VBA would be great for this.
I have tried something like this based on collating various snippets of code online and modifying it to my usage, but it is simply deleting my selected text (it thinks 'elements' is empty) and I am not sure what's going wrong or if there's an easier way to do this.
Sub FormatTextBeforeToAfter() Dim selectedRange As Range Dim formattedText As String Dim lines() As String Dim resultText As String Dim i As Integer ' Check if text is selected If Not Selection.Text = "" Then ' Get the selected range Set selectedRange = Selection.Range formattedText = selectedRange.Text ' Split the formattedText into lines lines = Split(formattedText, vbCrLf) ' Initialize the result text resultText = "" ' Loop through each line and format it For i = LBound(lines) To UBound(lines) ' Check if the line is not empty If Trim(lines(i)) <> "" Then Dim elements() As String elements = Split(lines(i), " - ") If UBound(elements) >= 1 Then ' Extract parts Dim domainPart As String Dim subdomainPart As String Dim taskStatementPart As String domainPart = "Domain: " & elements(1) & " - " & elements(0) If UBound(elements) >= 2 Then subdomainPart = "Sub-domain: " & Mid(elements(2), 1, 2) & " - " & Mid(elements(2), 3) ' Adjusted to capture the numeric part. End If If UBound(elements) >= 3 Then taskStatementPart = "Task Statement: " & Mid(elements(3), 1, 2) & " - " & Mid(elements(3), 3) ' Adjusted to capture the numeric part. End If ' Add the formatted parts to the result resultText = resultText & domainPart & vbCrLf & subdomainPart & vbCrLf & taskStatementPart & vbCrLf End If End If Next i ' Replace the selected text with the formatted result text selectedRange.Text = resultText Else MsgBox "No text is selected. Please select the text you want to format." End If End Sub
Any help would be greatly appreciated thank you!