Solved: Paragraph alignment formatting?

[defcon_3]

Hello,

I am finding a way to format this extracted table from a pdf file to an aligned result. Below if the sample.

Extracted format:

1
Hoi Hup Realty Pte Ltd, Investment Focus
Pte Ltd and Oriental Worldwide Investments
Inc
194,600,000.00 5,452.81
2
FCL Topaz Pte. Ltd., F.E. Lakeside Pte. Ltd.
and Sekisui House Singapore Pte. Ltd.
192,777,000.00 5,401.73
3 S L (Serangoon) Pte Ltd 180,800,000.00 5,066.13
4 UVD Pte. Ltd. 167,388,000.00 4,690.32
5
BBR Development Pte Ltd and Santarli
Corporation Pte Ltd
161,900,000.00 4,536.54
6
Meadows Bright Development Pte Ltd and
Hock Lian Seng Contractors Pte Ltd
155,600,000.00 4,360.01
7
Mezzo Development Pte Ltd 128,000,000.00 3,586.64

Expected Result:

1 Hoi Hup Realty Pte Ltd, Investment Focus Pte Ltd and Oriental Worldwide Investments Inc 194,600,000.00
2 FCL Topaz Pte. Ltd., F.E. Lakeside Pte. Ltd.and Sekisui House Singapore Pte. Ltd. 192,777,000.00
3 S L (Serangoon) Pte Ltd 180,800,000.00
4 UVD Pte. Ltd. 167,388,000.00
5 BBR Development Pte Ltd and Santarli Corporation Pte Ltd 161,900,000.00
6 Meadows Bright Development Pte Ltd and Hock Lian Seng Contractors Pte Ltd 155,600,000.00
7 Mezzo Development Pte Ltd 128,000,000.00

Notice as well that the last set of numbers should be removed like the below example:

194,600,000.00 5,452.81 to 194,600,000.00
5,452.81 was removed.

Is there any way to do this?

Thanks in advance.

[macropod]

Hi Defcon,

Your data are somewhat of a dog's breakfast, so to speak. Try:
Sub Demo()
Application.ScreenUpdating = False
Dim Rng As Range, i As Long, bStart As Boolean
With ActiveDocument
bStart = False
For i = .Paragraphs.Count To 2 Step -1
Set Rng = .Paragraphs(i - 1).Range
With Rng
.End = .End - 1
If Len(Split(.Text, " ")(0)) < 4 And IsNumeric(Split(.Text, " ")(0)) And IsNumeric(.Text) = False Then
bStart = True
ElseIf IsNumeric(.Text) = False Then
If bStart = True Then
bStart = False
Else
.InsertAfter " "
ActiveDocument.Paragraphs(i - 1).Range.Characters.Last.Delete
End If
Else
.InsertAfter " "
.Characters.Last.Next.Delete
bStart = True
End If
End With
Next
For i = 1 To .Paragraphs.Count
Set Rng = .Paragraphs(i).Range
With Rng
.End = .End - 1
If IsNumeric(Split(.Text, " ")(UBound(Split(.Text, " ")) - 1)) Then
If IsNumeric(Split(.Text, " ")(UBound(Split(.Text, " ")))) Then
.Text = Left(.Text, Len(.Text) - Len(Split(.Text, " ")(UBound(Split(.Text, " ")))) - 1)
End If
End If
End With
Next
End With
Application.ScreenUpdating = True
End Sub

[defcon_3]

Cool that works like a charm. Thanks a million :)

editted:
Would you mind explaining a bit so I can use it as based on other formats, coz I still have other formats to align?

Thanks again.

[macropod]

I'm re-posting the code with comments. HTH
Sub Demo()
Application.ScreenUpdating = False
Dim Rng As Range, i As Long, bStart As Boolean
With ActiveDocument
' Sset the initial state of the bStart flag
bStart = False
' Loop through the paragraphs, starting with the last
For i = .Paragraphs.Count To 2 Step -1
' Look at the preceding paragraph
Set Rng = .Paragraphs(i - 1).Range
' With the preceding paragraph ...
With Rng
' Get the content
.End = .End - 1
' Check whether the first word is a number less than 4 characters long and in the only content
If Len(Split(.Text, " ")(0)) < 4 And IsNumeric(Split(.Text, " ")(0)) And IsNumeric(.Text) = False Then
' If so, don't do anything to the paragraph, just set the bStart flag to True
bStart = True
' If the first word is NOT a number less than 4 characters long with no other content,
' check whether the content is just one number
ElseIf IsNumeric(.Text) = False Then
' If it's NOT just one number, check the state of the bStart flag
If bStart = True Then
' If True, don't do anything to the paragraph, just set the bStart flag to False
bStart = False
Else
' If False, insert a space at the end and delete the trailing paragraph break,
' so as to join this paragraph with the following one
.InsertAfter " "
ActiveDocument.Paragraphs(i - 1).Range.Characters.Last.Delete
End If
' If none of the above apply, insert a space at the end and delete the trailing paragraph break,
' so as to join this paragraph with the following one and set the bStart flag to True
Else
.InsertAfter " "
.Characters.Last.Next.Delete
bStart = True
End If
End With
Next
' Now that all the related content has been joined into one-paragraph units,
' delete the number at the end, provided the previous 'word' is also a number
For i = 1 To .Paragraphs.Count
Set Rng = .Paragraphs(i).Range
With Rng
.End = .End - 1
If IsNumeric(Split(.Text, " ")(UBound(Split(.Text, " ")) - 1)) Then
If IsNumeric(Split(.Text, " ")(UBound(Split(.Text, " ")))) Then
.Text = Left(.Text, Len(.Text) - Len(Split(.Text, " ")(UBound(Split(.Text, " ")))) - 1)
End If
End If
End With
Next
End With
Application.ScreenUpdating = True
End Sub

[defcon_3]

Thank you now I can read clearly the routine. :)

[defcon_3]

I have another query macropod. How can I make this code works only on selected text, paragraphs in the documents not the document as a whole.
Thank you :)

[macropod]

Change:

Dim Rng As Range, i As Long, bStart As Boolean
With ActiveDocument
to:

Dim Rng As Range, RngSel As Range, i As Long, bStart As Boolean
Set RngSel = Selection.Range
With RngSel
and change:

ActiveDocument.Paragraphs(i - 1).Range.Characters.Last.Delete
to:

RngSel.Paragraphs(i - 1).Range.Characters.Last.Delete

[defcon_3]

Ok to sum Up here it is.



Sub Demo()
Application.ScreenUpdating = False
Dim Rng As Range, RngSel As Range, i As Long, bStart As Boolean
Set RngSel = Selection.Range
With RngSel
bStart = False
For i = .Paragraphs.Count To 2 Step -1
Set Rng = .Paragraphs(i - 1).Range
With Rng
.End = .End - 1
If Len(Split(.Text, " ")(0)) < 4 And IsNumeric(Split(.Text, " ")(0)) And IsNumeric(.Text) = False Then
bStart = True
ElseIf IsNumeric(.Text) = False Then
If bStart = True Then
bStart = False
Else
.InsertAfter " "
RngSel.Paragraphs(i - 1).Range.Characters.Last.Delete
End If
Else
.InsertAfter " "
.Characters.Last.Next.Delete
bStart = True
End If
End With
Next
For i = 1 To .Paragraphs.Count
Set Rng = .Paragraphs(i).Range
With Rng
.End = .End - 1
If IsNumeric(Split(.Text, " ")(UBound(Split(.Text, " ")) - 1)) Then
If IsNumeric(Split(.Text, " ")(UBound(Split(.Text, " ")))) Then
.Text = Left(.Text, Len(.Text) - Len(Split(.Text, " ")(UBound(Split(.Text, " ")))) - 1)
End If
End If
End With
Next
End With
Application.ScreenUpdating = True
End Sub


Thanks so much. SOLVED :)