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]

 

[Peter_SSs]

macOS High Sierra 10.13.16
Excel 2018 Excel for Mac 16.17

I don't have a Mac but I don't think that you can use VBScript as per the highlighted line of code on a Mac.
Have you tried the formula approach suggested?

BTW, best to state up front that you are using a Mac. Whilst there are a number of Mac users that help on the forum, by far the majority are PC users so it would help you get the right people helping quicker.

 

[bear1]

Hi Peter,

Thank you once again for trying to help me. I can't make the formula approach work either for some reason. I tried both with 'TEXTJOIN' and without. Neither worked.

What else can I do? I prefer the macro (vba) approach and you can run macro's on the Mac version of Excel.

kind regards

 

[Peter_SSs]

I can't make the formula approach work either for some reason. I tried both with 'TEXTJOIN' and without. Neither worked.

"neither worked" doesn't give me much to go on.
For the sample data in cell A2 of post 7, and using the formulas I suggested for C2:L2, what result did the formulas produce for you in
C2?
D2?
E2?
etc

Perhaps you could even use the Forum Tools Add-in found via the Look here link in my signature block below to show us what happened?

You can see in post 7 that those formula did work for me (at least for that sample data)**

** Except perhaps for row 7 which might need some further explanation. In post 3 that 3rd last row contained the text "Sternline" in the 'Full Original Data' but that text did not appear in the 'How I Want it' section. What is going on there?

you can run macro's on the Mac version of Excel.

Yes you can, but not some particular codes as I understand it. One of them is
CreateObject("VBScript.RegExp")

 

[Peter_SSs]

** Except perhaps for row 7 which might need some further explanation. In post 3 that 3rd last row contained the text "Sternline" in the 'Full Original Data' but that text did not appear in the 'How I Want it' section. What is going on there?

Subject to resolution of the above issue, this code seems to be going pretty close to the mark. Hopefully it will run on a Mac.

Sub Parse_Text_v2()
  Dim a As Variant, b As Variant, bits As Variant
  Dim i As Long, j As Long, ubbits As Long
  Dim s As String
  Dim bNumsDone As Boolean
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 10)
  For i = 1 To UBound(a)
    bNumsDone = False
    bits = Split(a(i, 1))
    ubbits = UBound(bits)
    For j = 0 To ubbits - 3
      s = bits(j)
      Select Case s
        Case "S", "B", "S/B"
          b(i, 2) = s
        Case "1A", "1B"
          b(i, 3) = s
        Case "2", "2/3", "3"
          b(i, 4) = CStr(s)
        Case Else
          If s Like "*#.###" Then
            b(i, 5) = Val(s)
            b(i, 6) = Val(bits(j + 1))
            b(i, 7) = Val(bits(j + 2))
            b(i, 8) = bits(j + 3)
            j = j + 3
            bNumsDone = True
          Else
            If Not bNumsDone Then
              b(i, 1) = LTrim(b(i, 1) & " " & s)
            Else
              b(i, 8) = b(i, 8) & " " & s
            End If
          End If
      End Select
    Next j
    b(i, 9) = bits(ubbits - 2) & " " & bits(ubbits - 1)
    b(i, 10) = bits(ubbits)
  Next i
  With Range("C2").Resize(UBound(b, 1), UBound(b, 2))
    .Columns(4).NumberFormat = "@"
    .Value = b
    .Rows(0).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
Sheet2

 

[bear1]

Hi Peter,

Okay I have got the formula to work and it's working well. So thank you very, very much for that. It's not perfect, but good enough.

I will have a go at the Macro you suggest above now to see if i can get that to work then i have both options.

Thank you for all your help so far.

Kind regards

 

[Peter_SSs]

It's not perfect, but good enough.

If you can provide sample data where it doesn't work and explain where it goes wrong for that data, the formulas may be able to be refined to give better results.

.. and if posting back, please resolve the issue about the data in row 7 above.
Is the A7 data correct?
If it is correct, what should be in columns J, K & L and why?
If it is not correct, what should it be?

 

[bear1]

1. Okay, great news. Both the formulas and the new Macro from post #14 work, so i have options now.

2. With regards to Row 7, the A7 data is correct, it's just the way the data providers have decided to send it. So for most scenarios they send it as location(Open) (e.g. Amsterdam) followed by date (DTD), followed by Last 3 Cargoes but in Row 7 they have inserted 'Sternline' between DTD (K) DTD and Last 3 Cargoes (L), which skews the columns. They seem to be do this regulalry for certain vessels so it would be good to sort.

So for example they have done it again today [TABLE="width: 936"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Hafnia Robson 3 39.819 42.221 2004 Amsterdam 2. Oct Sternline LVN + RAF/go/ulsd

3. The other problem i get is that the data providers sometimes add a second location and date so for example in below they say 'if New York 3. Oct USG 9. Oct' i want to strip out the second location and date but keep 'Sternline'
[TABLE="width: 936"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Hafnia Lene 2/3 49.999 53.448 2015 if New York 3. Oct USG 9. Oct ums/nap/go[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for all your kind help.

 

[Peter_SSs]

1. Okay, great news. Both the formulas and the new Macro from post #14 work, so i have options now.

Good news - subject to below.

2. With regards to Row 7, the A7 data is correct, it's just the way the data providers have decided to send it. So for most scenarios they send it as location(Open) (e.g. Amsterdam) followed by date (DTD), followed by Last 3 Cargoes but in Row 7 they have inserted 'Sternline' between DTD (K) DTD and Last 3 Cargoes (L), which skews the columns. They seem to be do this regulalry for certain vessels so it would be good to sort.

So for example they have done it again today [TABLE="width: 936"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Hafnia Robson 3 39.819 42.221 2004 Amsterdam 2. Oct Sternline LVN + RAF/go/ulsd

3. The other problem i get is that the data providers sometimes add a second location and date so for example in below they say 'if New York 3. Oct USG 9. Oct' i want to strip out the second location and date but keep 'Sternline'
[TABLE="width: 936"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Hafnia Lene 2/3 49.999 53.448 2015 if New York 3. Oct USG 9. Oct ums/nap/go[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

You haven't specifically said what results you want for 2. and 3. above. Do the formulas/macro produce the results you want for that raw data? If not, please clearly specify the required results for each case.

 

[bear1]

Hi Again Peter,

Sorry to be a pain but is it possible to do the same Excel formula's in a format for Google sheets? So below doesn't work in Google sheets, do you know how i can make it work. The reason for using Google Sheets is that i have an email parser set up that outputs to Google Sheets and that was pretty easy to set up.

Spreadsheet Formulas

Cell Formula C2 =LEFT(A2,FIND(" "&TRIM(D2&" "&E2&" "&F2),A2)-1) D2 =IFERROR(TRIM(MID(SUBSTITUTE(A2," "," "),AGGREGATE(15,6,FIND({" B "," S "," S/B "},SUBSTITUTE(A2," "," ")),1)+1,3)),"") E2 =IFERROR(TRIM(MID(A2,AGGREGATE(15,6,FIND({" 1B "," 1A "},A2),1)+1,3)),"") F2 =IFERROR(TRIM(MID(SUBSTITUTE(A2," "," "),AGGREGATE(15,6,FIND({" 2 "," 3 "," 2/3 "},SUBSTITUTE(A2," "," ")),1)+1,3)),"") G2 =MID(SUBSTITUTE(SUBSTITUTE(A2,TEXTJOIN(" ",TRUE,C2,D2,E2,F2)&" ","")," ",REPT(" ",50)),1,50)+0 H2 =MID(SUBSTITUTE(SUBSTITUTE(A2,TEXTJOIN(" ",TRUE,C2,D2,E2,F2)&" ","")," ",REPT(" ",50)),50,50)+0 I2 =MID(SUBSTITUTE(SUBSTITUTE(A2,TEXTJOIN(" ",TRUE,C2,D2,E2,F2)&" ","")," ",REPT(" ",50)),100,50)+0 J2 =SUBSTITUTE(REPLACE(A2,1,FIND(" "&I2&" ",A2)+5,"")," "&K2&" "&L2,"") K2 =TRIM(MID(A2,FIND(". ",A2)-2,7)) L2 =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50)) <tbody> </tbody>

<tbody>
</tbody>

All the best
Bear 1

Thanks again for all the help

 

[Peter_SSs]

Sorry to be a pain but is it possible to do the same Excel formula's in a format for Google sheets?

I'm afraid I cannot help with that, I don't use Google Sheets (& this is an Excel forum. )