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

Noodles90

New Member
Joined
Sep 14, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:
CellTitleText
A2Engine size - Displacement - Engine capacity:999 cm3 or 61 cu-in
A3Body: SUV / TT Num. of Doors:
5​
A4Wheelbase:267.4 cm or 105.28 inches
A5Length:434.1 cm or 170.91 inches
A6Width:180.4 cm or 71.02 inches
A7Height:169.3 cm or 66.65 inches
A8Front Axle156.3 cm or 61.54 inches
A9Rear Axle157 cm or 61.81 inches
A10Ground clearance:21.0 cm / 8.27 inches
A11Ground clearance:21.0 cm / 8.27 inches
A12Max. Towing Capacity Weight1400 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:
AB
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 Axle156.3 cm or 61.54 inches
9​
Rear Axle157 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 Weight1400 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

Well-known Member
Joined
Nov 26, 2020
Messages
1,618
Office Version
  1. 2016
Platform
  1. Windows
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 macroOrder in the sentence
Ground clearance:Ground clearance:
Ground clearance:Max. Towing Capacity Weight
Max. Towing Capacity WeightGround 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. (y)

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. :unsure: I was rushing home after work to avoid traffic 😁
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Noodles90

New Member
Joined
Sep 14, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Sep 14, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Good to hear feedback. At least you get @Akuini one works. (y)

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. :unsure: 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
 

Forum statistics

Threads
1,141,584
Messages
5,707,209
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top