Full Original Data:
[TABLE="width: 1019"]
<colgroup><col></colgroup><tbody>[TR]
[TR]
[TD]Hafnia Lotte B 2/3 49.999 53.448 2017 ARA for orders ex Las Palmas 21. Sep ums/nap/ulsd [/TD]
[/TR]
[TR]
[TD]Hafnia Sunda 1B 2/3 39.067 42.506 2015 Hamburg 21. Sep ulsd/ulsd/ulsd [/TD]
[/TR]
[TR]
</tbody>[/TABLE]
OK, thanks.The columns can be limited to only the following:
Status: S, B, S/B
Ice: 1A, 1B
IMO: 2, 2/3, 3
Sub Parse_Text()
Dim RX As Object
Dim a As Variant
Dim i As Long
Set RX = CreateObject("VBScript.RegExp")
RX.Pattern = "(^.+?)( ?)(B|S|S/B)*( ?)(1A|1B)*( ?)(2|2/3|3)*( )(\d+\.\d{3})( )(\d+\.\d{3})( )(\d{4})( )(.*)( )(\d{2}\. [A-Z][a-z]{2})( )(.*$)" '(\d+\. [A-Z][a-z]{3})( )(.+$)"
a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
For i = 1 To UBound(a)
a(i, 1) = RX.Replace(a(i, 1), "$1;$3;$5;$7;$9;$11;$13;$15;$17;$19")
Next i
With Range("C2").Resize(UBound(a))
.Value = a
.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Semicolon:=True, Other:=False, FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 2), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1))
.Offset(-1).Resize(1, 10).Value = Array("Vessel Name", "Status", "ICE", "IMO", "DWT", "CBM", "Built", "Open", "DTD", "Last 3 cargoes")
.CurrentRegion.Columns.AutoFit
End With
End Sub
Excel Workbook | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Vessel Name ICE IMO DWT CBM Built Open DTD Last 3 cargoes | Vessel Name | Status | ICE | IMO | DWT | CBM | Built | Open | DTD | Last 3 cargoes | |||
2 | Hafnia Nordica B 49.999 59.100 2010 ARA for orders ex New York 20. Sep UKC 20. Sep ums/nap/jet | Hafnia Nordica | B | 49.999 | 59.1 | 2010 | ARA for orders ex New York 20. Sep UKC | 20. Sep | ums/nap/jet | |||||
3 | Hafnia Lotte B 2/3 49.999 53.448 2017 ARA for orders ex Las Palmas 21. Sep ums/nap/ulsd | Hafnia Lotte | B | 2/3 | 49.999 | 53.448 | 2017 | ARA for orders ex Las Palmas | 21. Sep | ums/nap/ulsd | ||||
4 | Hafnia Sunda 1B 2/3 39.067 42.506 2015 Hamburg 21. Sep ulsd/ulsd/ulsd | Hafnia Sunda | 1B | 2/3 | 39.067 | 42.506 | 2015 | Hamburg | 21. Sep | ulsd/ulsd/ulsd | ||||
5 | Nordic Hanne S/B 2 38.395 43.444 2010 Turkey 18. Sep ulsd/ulsd/jet | Nordic Hanne | S/B | 2 | 38.395 | 43.444 | 2010 | Turkey | 18. Sep | ulsd/ulsd/jet | ||||
6 | Sikinos 1A 3 37.620 41.614 2006 Eleusis 20. Sep fo/fo/fo | Sikinos | 1A | 3 | 37.62 | 41.614 | 2006 | Eleusis | 20. Sep | fo/fo/fo | ||||
7 | Alice S 2/3 39.316 43.871 2013 Gaeta 20. Sep Sternline ulsd/ulsd/ums | Alice | S | 2/3 | 39.316 | 43.871 | 2013 | Gaeta | 20. Sep | Sternline ulsd/ulsd/ums | ||||
8 | Seameridian B 49.999 56.845 2011 Singapore 18. Sep jet/ulsd/ums | Seameridian | B | 49.999 | 56.845 | 2011 | Singapore | 18. Sep | jet/ulsd/ums | |||||
9 | MP MR Tanker 1 2/3 51.745 54.250 2011 if Kawasaki 24. Sep Naphtha/go/ums | MP MR Tanker 1 | 2/3 | 51.745 | 54.25 | 2011 | if Kawasaki | 24. Sep | Naphtha/go/ums | |||||
Sheet3 |
Excel Workbook | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Vessel Name ICE IMO DWT CBM Built Open DTD Last 3 cargoes | Vessel Name | Status | ICE | IMO | DWT | CBM | Built | Open | DTD | Last 3 cargoes | |||
2 | Hafnia Nordica B 49.999 59.100 2010 ARA for orders ex New York 20. Sep UKC 20. Sep ums/nap/jet | Hafnia Nordica | B | 49.999 | 59.1 | 2010 | ARA for orders ex New York 20. Sep UKC | 20. Sep | ums/nap/jet | |||||
3 | Hafnia Lotte B 2/3 49.999 53.448 2017 ARA for orders ex Las Palmas 21. Sep ums/nap/ulsd | Hafnia Lotte | B | 2/3 | 49.999 | 53.448 | 2017 | ARA for orders ex Las Palmas | 21. Sep | ums/nap/ulsd | ||||
4 | Hafnia Sunda 1B 2/3 39.067 42.506 2015 Hamburg 21. Sep ulsd/ulsd/ulsd | Hafnia Sunda | 1B | 2/3 | 39.067 | 42.506 | 2015 | Hamburg | 21. Sep | ulsd/ulsd/ulsd | ||||
5 | Nordic Hanne S/B 2 38.395 43.444 2010 Turkey 18. Sep ulsd/ulsd/jet | Nordic Hanne | S/B | 2 | 38.395 | 43.444 | 2010 | Turkey | 18. Sep | ulsd/ulsd/jet | ||||
6 | Sikinos 1A 3 37.620 41.614 2006 Eleusis 20. Sep fo/fo/fo | Sikinos | 1A | 3 | 37.62 | 41.614 | 2006 | Eleusis | 20. Sep | fo/fo/fo | ||||
7 | Alice S 2/3 39.316 43.871 2013 Gaeta 20. Sep Sternline ulsd/ulsd/ums | Alice | S | 2/3 | 39.316 | 43.871 | 2013 | Gaeta 20. Sep Sternline ulsd/ulsd/ums | 20. Sep | ulsd/ulsd/ums | ||||
8 | Seameridian B 49.999 56.845 2011 Singapore 18. Sep jet/ulsd/ums | Seameridian | B | 49.999 | 56.845 | 2011 | Singapore | 18. Sep | jet/ulsd/ums | |||||
9 | MP MR Tanker 1 2/3 51.745 54.250 2011 if Kawasaki 24. Sep Naphtha/go/ums | MP MR Tanker 1 | 2/3 | 51.745 | 54.25 | 2011 | if Kawasaki | 24. Sep | Naphtha/go/ums | |||||
Sheet4 |