Segmenting text contained in one cell according to the titles there contained.

[Noodles90]

Hi, I am new in the ways of VBA coding, and I have been mostly adapting recorded code. The most that I can do is to define variables, make relative references and simple loops xD
I am working with catalogs for cars and car parts. For one particular distributor, the only way of obtaining the full specs from the system is all altogether.
In this way, we need to separate the text manually, and this process is a quite time-consuming process (The texts are way longer than what is presented in the example).

Example 1, (let's say this text is pasted in cell A1):

Engine size - Displacement - Engine capacity: 999 cm3 or 61 cu-in Body: SUV / TT Num. of Doors: 5 Wheelbase: 267.4 cm or 105.28 inches Length: 434.1 cm or 170.91 inches Width: 180.4 cm or 71.02 inches Height: 169.3 cm or 66.65 inches Front Axle 156.3 cm or 61.54 inches Rear Axle 157 cm or 61.81 inches Ground clearance: 21.0 cm / 8.27 inches Ground clearance: 21.0 cm / 8.27 inches Max. Towing Capacity Weight 1400 Kg or 3086.47 lbs

Final result for Example 1:

Cell	Title	Text
A2	Engine size - Displacement - Engine capacity:	999 cm3 or 61 cu-in
A3	Body: SUV / TT Num. of Doors:	5​
A4	Wheelbase:	267.4 cm or 105.28 inches
A5	Length:	434.1 cm or 170.91 inches
A6	Width:	180.4 cm or 71.02 inches
A7	Height:	169.3 cm or 66.65 inches
A8	Front Axle	156.3 cm or 61.54 inches
A9	Rear Axle	157 cm or 61.81 inches
A10	Ground clearance:	21.0 cm / 8.27 inches
A11	Ground clearance:	21.0 cm / 8.27 inches
A12	Max. Towing Capacity Weight	1400 Kg or 3086.47 lbs

Now the tricky part is that there is no marker distinguishing the titles from the text, some have ":" at the end, some not...
Also, although the titles appear always in the same order, if there is no information regarding one of these, it simply does not appear in the text.

Example 2 (Equal to example 1, but with no Front Axle information):

Engine size - Displacement - Engine capacity: 999 cm3 or 61 cu-in Body: SUV / TT Num. of Doors: 5 Wheelbase: 267.4 cm or 105.28 inches Length: 434.1 cm or 170.91 inches Width: 180.4 cm or 71.02 inches Height: 169.3 cm or 66.65 inches Rear Axle 157 cm or 61.81 inches Ground clearance: 21.0 cm / 8.27 inches Ground clearance: 21.0 cm / 8.27 inches Max. Towing Capacity Weight 1400 Kg or 3086.47 lbs

As there are no markers for the titles, I am thinking that the best is to set the titles as constants.
This way if more titles are introduced into the system, I can simply add them to the code, and add a number to the Integer:

Code start example:

Dim Const Title1 = "Engine size - Displacement - Engine capacity:"
Dim Const Title2 = "Body: SUV / TT Num. of Doors:"
Dim Const Title3 = "Wheelbase:"

Dim x As Integer
For x = 1 To 3

Having these constants defined, it would be possible to do the following:
Step 1 -Search for the first title in the code; If not found, search for title2; If found, paste it in the cell below;
Step 2 -Search for the following title; if found, paste the text contained between the first found title and the second found title, one cell down, one cell to the left; If not found, search the next title;
Step3 - Loop

And repeating this process untill all titles present in the original cell are separated below:

	A	B
1​	Engine size - Displacement - Engine capacity: 999 cm3 or 61 cu-in Body: SUV / TT Num. of Doors: 5 Wheelbase: 267.4 cm or 105.28 inches Length: 434.1 cm or 170.91 inches Width: 180.4 cm or 71.02 inches Height: 169.3 cm or 66.65 inches Front Axle 156.3 cm or 61.54 inches Rear Axle 157 cm or 61.81 inches Ground clearance: 21.0 cm / 8.27 inches Ground clearance: 21.0 cm / 8.27 inches Max. Towing Capacity Weight 1400 Kg or 3086.47 lbs
2​	Engine size - Displacement - Engine capacity:	999 cm3 or 61 cu-in
3​	Body: SUV / TT Num. of Doors:	5​
4​	Wheelbase:	267.4 cm or 105.28 inches
5​	Length:	434.1 cm or 170.91 inches
6​	Width:	180.4 cm or 71.02 inches
7​	Height:	169.3 cm or 66.65 inches
8​	Front Axle	156.3 cm or 61.54 inches
9​	Rear Axle	157 cm or 61.81 inches
10​	Ground clearance:	21.0 cm / 8.27 inches
11​	Ground clearance:	21.0 cm / 8.27 inches
12​	Max. Towing Capacity Weight	1400 Kg or 3086.47 lbs

My question is if this is possible, or if there is a better way of doing it, I am searching for ideas.
Can someone advise if I am thinking well here?

 

[Zot]

This is much more than what I was expecting - Awesome macros - Thanks xD
@Zot - Your code unfortunately is not working well here, the titles are not pasted, and is not catching specs not related to dimensions. Thank you nonetheless.
@Akuini - Your macro is doing the job, the only issue is that the order by which the specs appear in the text is not the same as the order in the results.
This for the titles which are repeated, as for example "Ground clearance:". In the results, these titles appear together and not in the same order:

Order by the macro	Order in the sentence
Ground clearance:	Ground clearance:
Ground clearance:	Max. Towing Capacity Weight
Max. Towing Capacity Weight	Ground clearance:

This creates problems as there are many repeated titles in the long sentences I have, because the same titles appear for different categories. Sometimes with different specs.
Is there a fix for this?

@Peter_SSs Your macro works very well, but unfortunately I cannot use it as a VBA warning appears "Too many line continuations" when I add more than 25 Titles.
Is there a workaround this problem?

Good to hear feedback. At least you get @Akuini one works.

Yes. I did not write title since I know you can figure it out because I 'm sure you have may rows of data to process and not sure how you want to lay out the result.

I can only test against the data provided. Unless I overlooked something but it was working and can be simplified. I was rushing home after work to avoid traffic ?

 

[Noodles90]

There is no need to put each Title on a new line - I just did that for clarity. Here I have reduced them to two lines but it could even be made to one line. The titles just need to be part of a string, separated by "|" characters. How many titles do you actually have?

My code could also easily grab the titles from a list within a worksheet like Akuini's does if you would prefer that. I did it from withing the code as that was what you were attempting to do yourself.

Anyway, give this a go, by adding your extra Titles before the closing parenthesis near the end of my pattern line. If you do continue to any new lines, just follow the syntax of the line continuation that I used.

Sub SplitEmUP()
  Dim RX As Object
  Dim a As Variant, b As Variant, itm
  Dim i As Long, j As Long, k As Long, MaxRws As Long, MaxCols As Long
 
  ReDim b(1 To Rows.Count, 1 To 2)
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\s*(Engine size - Displacement - Engine capacity:|Body: SUV / TT Num. of Doors:|Wheelbase:|Length:|Width:|Height:|Front Axle|" & _
                "Rear Axle|Ground clearance:|Max. Towing Capacity Weight)\s*"
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
  For i = 1 To UBound(a)
    j = 0
    k = k + 2
    If k > MaxCols Then
      MaxCols = k
      ReDim Preserve b(1 To UBound(b), 1 To MaxCols)
    End If
    For Each itm In Split(Mid(RX.Replace(a(i, 1), "#$1@"), 2), "#")
      j = j + 1
      b(j, k - 1) = Split(itm, "@")(0): b(j, k) = Split(itm, "@")(1)
    Next itm
    If j > MaxRws Then MaxRws = j
  Next i
  Application.ScreenUpdating = False
  With Range("C2").Resize(MaxRws, MaxCols)
    .Value = b
    .Columns.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

Ok, I was not familiarised with the way of writting in this RX.Pattern expression. Up to now around 40 titles are there included. And it is working flawlessly, thanks you!

 

[Noodles90]

Good to hear feedback. At least you get @Akuini one works.

Yes. I did not write title since I know you can figure it out because I 'm sure you have may rows of data to process and not sure how you want to lay out the result.

I can only test against the data provided. Unless I overlooked something but it was working and can be simplified. I was rushing home after work to avoid traffic ?

Yeah, my bad to have included a small data set. Anyways how can someone write code at this level while rushing is something that blows my mind... I was just expecting ideas, and I got multiple sets of code doing the task perfectly xD

 

[Peter_SSs]

Up to now around 40 titles are there included. And it is working flawlessly, thanks you!

You are welcome. Thanks for the confirmation.