Split text and numbers in a text string

[bear1]

I want split/separate a text string into Text and Numbers

Example: [FONT=&quot]Hafnia Nordica S/B 49.999 59.100 2010 off Amsterdam 25. Sep UKC 25. Sep ums/nap/jet

I want to split numbers and text into different columns.

Please help[/FONT]

 

[Joe4]

Welcome to the Board!

So, what exactly do you want your expected result to look like?
How many columns will you be left with?
Note that if you use Text to Columns, and use a space as the delimiter, it will separate each word/number into its own column.
Is that what you are after?

 

[bear1]

Thanks for coming back to me Joe4.

I have used Text to Columns and the problem is it needs to be more intelligent than that. I have put commas in below to show where i want the column splits/delimiter. There are no comma's in the original so i can't use that as a delimiter. It needs to recognise the end of numbers and the beginning of text, is the only way i can think to solve it (it's not perfect but i think can be good enough). Below is my last example with comma's and below that I have pasted the full original data and below that again is the final result I want (10 Columns).

Example: Hafnia Nordica, S/B, 49.999, 59.100, 2010, off Amsterdam, 25. Sep, UKC, 25. Sep, ums/nap/jet

Full Original Data:

Vessel Name ICE IMO DWT CBM Built Open DTD Last 3 cargoes

Hafnia Nordica B 49.999 59.100 2010 ARA for orders ex New York 20. Sep UKC 20. Sep ums/nap/jet

Hafnia Lotte B 2/3 49.999 53.448 2017 ARA for orders ex Las Palmas 21. Sep ums/nap/ulsd

Hafnia Sunda 1B 2/3 39.067 42.506 2015 Hamburg 21. Sep ulsd/ulsd/ulsd

Nordic Hanne S/B 2 38.395 43.444 2010 Turkey 18. Sep ulsd/ulsd/jet

Sikinos 1A 3 37.620 41.614 2006 Eleusis 20. Sep fo/fo/fo

Alice S 2/3 39.316 43.871 2013 Gaeta 20. Sep Sternline ulsd/ulsd/ums

Seameridian B 49.999 56.845 2011 Singapore 18. Sep jet/ulsd/ums

MP MR Tanker 1 2/3 51.745 54.250 2011 if Kawasaki 24. Sep Naphtha/go/ums

<colgroup><col></colgroup><tbody>
</tbody>

How I want it:

Vessel Name	Status	ICE	IMO	DWT	CBM	Built	Open	DTD	Last 3 cargoes
Hafnia Nordica	B			49.999	59.100	2010	ARA for orders ex New York	20. Sep	ums/nap/jet
Hafnia Lotte	B		2/3	49.999	53.448	2017	ARA for orders ex Las Palmas	21. Sep	ums/nap/ulsd
Hafnia Sunda		1B	2/3	39.067	42.506	2015	Hamburg 21. Sep	21. Sep	ulsd/ulsd/ulsd
Nordic Hanne	S/B		2	38.395	43.444	2010	Turkey	18. Sep	ulsd/ulsd/jet
Sikinos		1A	3	37.620	41.614	2006	Eleusis 20. Sep	20. Sep	fo/fo/fo
Alice	S		2/3	39.316	43.871	2013	Gaeta	20. Sep	ulsd/ulsd/ums
Seameridian	B			49.999	56.845	2011	Singapore	18. Sep	jet/ulsd/ums
MP MR Tanker 1			2/3	51.745	54.250	2011	if Kawasaki	24. Sep	Naphtha/go/ums

<colgroup><col><col span="6"><col><col><col></colgroup><tbody>
</tbody>

 

[Peter_SSs]

Provided you are happy to use vba I think the results can be at least partially, and possibly fully, achieved.
Depends on the answers to the questions below for a start. There is no logical way from just analysing the text to determine that the two red items do in different columns. So ..
- Is there a list of letters/codes that would only go in the 'Status' columns?
- Is there a list of items that would only go in the 'ICE' column?
- Is there a list of items that would only go in the 'IMO' column?

Full Original Data:

Hafnia Lotte B 2/3 49.999 53.448 2017 ARA for orders ex Las Palmas 21. Sep ums/nap/ulsd

Hafnia Sunda 1B 2/3 39.067 42.506 2015 Hamburg 21. Sep ulsd/ulsd/ulsd

<colgroup><col></colgroup><tbody>
[TR]

</tbody>

[TR]

 

[bear1]

Hi Peter, thank you for the reply.

The columns can be limited to only the following:

Status: S, B, S/B
Ice: 1A, 1B
IMO: 2, 2/3, 3

I don’t know anything about vba? Is that marcos? I’m good with formulas but not macros.

Kind regards

 

[Peter_SSs]

The columns can be limited to only the following:

Status: S, B, S/B
Ice: 1A, 1B
IMO: 2, 2/3, 3

OK, thanks.

This may be possible with formulas but I think a macro (vba) will be easiest. To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

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

I assume you have a few typos in either your sample data or the expected results but for my sample data in column A below, the above code produced the results in columns C:L

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

 

[Peter_SSs]

If your sample data is representative, then I think a formula approach is feasible. You could try each of these, copied down.

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

If you do not have the TEXTJOIN function for columns G:I, these could be tried instead:
G2: =MID(SUBSTITUTE(REPLACE(A2,1,LEN(TRIM(C2&" "&D2&" "&E2&" "&F2))+1,"")," ",REPT(" ",50)),1,50)+0
H2: =MID(SUBSTITUTE(REPLACE(A2,1,LEN(TRIM(C2&" "&D2&" "&E2&" "&F2))+1,"")," ",REPT(" ",50)),50,50)+0
I2: =MID(SUBSTITUTE(REPLACE(A2,1,LEN(TRIM(C2&" "&D2&" "&E2&" "&F2))+1,"")," ",REPT(" ",50)),100,50)+0

 

[bear1]

Hi Peter,

Thank you, i tired the Macro approach but got the following error when i run it:

Run-time error '429':

ActiveX component can't create object

I clicked Debug and it highlighted this line (Red text below)

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

 

[Peter_SSs]

What version of Excel are you using & what operating system?

 

[bear1]

macOS High Sierra 10.13.16
Excel 2018 Excel for Mac 16.17