Split text and numbers in a text string

B

bear1

New Member
Joined
Jan 11, 2012
Messages
22
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]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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?
 
Upvote 0
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:
[TABLE="width: 1019"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Vessel Name ICE IMO DWT CBM Built Open DTD Last 3 cargoes [/TD]
[/TR]
[TR]
[TD]Hafnia Nordica B 49.999 59.100 2010 ARA for orders ex New York 20. Sep UKC 20. Sep ums/nap/jet [/TD]
[/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]
[TD]Nordic Hanne S/B 2 38.395 43.444 2010 Turkey 18. Sep ulsd/ulsd/jet [/TD]
[/TR]
[TR]
[TD]Sikinos 1A 3 37.620 41.614 2006 Eleusis 20. Sep fo/fo/fo [/TD]
[/TR]
[TR]
[TD]Alice S 2/3 39.316 43.871 2013 Gaeta 20. Sep Sternline ulsd/ulsd/ums [/TD]
[/TR]
[TR]
[TD]Seameridian B 49.999 56.845 2011 Singapore 18. Sep jet/ulsd/ums [/TD]
[/TR]
[TR]
[TD]MP MR Tanker 1 2/3 51.745 54.250 2011 if Kawasaki 24. Sep Naphtha/go/ums [/TD]
[/TR]
</tbody>[/TABLE]



How I want it:[TABLE="width: 1208"]
<colgroup><col><col span="6"><col><col><col></colgroup><tbody>[TR]
[TD]Vessel Name[/TD]
[TD]Status[/TD]
[TD] ICE[/TD]
[TD] IMO[/TD]
[TD] DWT[/TD]
[TD] CBM[/TD]
[TD] Built[/TD]
[TD] Open[/TD]
[TD] DTD[/TD]
[TD] Last 3 cargoes [/TD]
[/TR]
[TR]
[TD]Hafnia Nordica[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD] 49.999[/TD]
[TD] 59.100[/TD]
[TD] 2010[/TD]
[TD] ARA for orders ex New York[/TD]
[TD]20. Sep[/TD]
[TD] ums/nap/jet [/TD]
[/TR]
[TR]
[TD]Hafnia Lotte[/TD]
[TD]B[/TD]
[TD][/TD]
[TD] 2/3[/TD]
[TD] 49.999[/TD]
[TD] 53.448[/TD]
[TD] 2017[/TD]
[TD] ARA for orders ex Las Palmas[/TD]
[TD]21. Sep[/TD]
[TD] ums/nap/ulsd [/TD]
[/TR]
[TR]
[TD]Hafnia Sunda[/TD]
[TD][/TD]
[TD] 1B[/TD]
[TD] 2/3[/TD]
[TD] 39.067[/TD]
[TD] 42.506[/TD]
[TD] 2015[/TD]
[TD] Hamburg 21. Sep[/TD]
[TD]21. Sep[/TD]
[TD] ulsd/ulsd/ulsd [/TD]
[/TR]
[TR]
[TD]Nordic Hanne[/TD]
[TD]S/B[/TD]
[TD][/TD]
[TD] 2[/TD]
[TD] 38.395[/TD]
[TD] 43.444[/TD]
[TD] 2010[/TD]
[TD] Turkey[/TD]
[TD]18. Sep[/TD]
[TD] ulsd/ulsd/jet [/TD]
[/TR]
[TR]
[TD]Sikinos[/TD]
[TD][/TD]
[TD] 1A[/TD]
[TD] 3[/TD]
[TD] 37.620[/TD]
[TD] 41.614[/TD]
[TD] 2006[/TD]
[TD] Eleusis 20. Sep[/TD]
[TD]20. Sep[/TD]
[TD] fo/fo/fo [/TD]
[/TR]
[TR]
[TD]Alice[/TD]
[TD]S[/TD]
[TD][/TD]
[TD] 2/3[/TD]
[TD] 39.316[/TD]
[TD] 43.871[/TD]
[TD] 2013[/TD]
[TD] Gaeta[/TD]
[TD]20. Sep[/TD]
[TD] ulsd/ulsd/ums[/TD]
[/TR]
[TR]
[TD]Seameridian[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD] 49.999[/TD]
[TD] 56.845[/TD]
[TD] 2011[/TD]
[TD] Singapore[/TD]
[TD]18. Sep[/TD]
[TD] jet/ulsd/ums[/TD]
[/TR]
[TR]
[TD]MP MR Tanker 1[/TD]
[TD][/TD]
[TD][/TD]
[TD] 2/3[/TD]
[TD] 51.745[/TD]
[TD] 54.250[/TD]
[TD] 2011[/TD]
[TD] if Kawasaki[/TD]
[TD]24. Sep[/TD]
[TD] Naphtha/go/ums [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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?

bear1 said:
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]
Click to expand...
 
Last edited:
Upvote 0
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
 
Upvote 0
bear1 said:
The columns can be limited to only the following:

Status: S, B, S/B
Ice: 1A, 1B
IMO: 2, 2/3, 3
Click to expand...
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)

Code: 
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
ABCDEFGHIJKL
1Vessel Name ICE IMO DWT CBM Built Open DTD Last 3 cargoesVessel NameStatusICEIMODWTCBMBuiltOpenDTDLast 3 cargoes
2Hafnia Nordica B 49.999 59.100 2010 ARA for orders ex New York 20. Sep UKC 20. Sep ums/nap/jetHafnia NordicaB49.99959.12010ARA for orders ex New York 20. Sep UKC20. Sepums/nap/jet
3Hafnia Lotte B 2/3 49.999 53.448 2017 ARA for orders ex Las Palmas 21. Sep ums/nap/ulsdHafnia LotteB2/349.99953.4482017ARA for orders ex Las Palmas21. Sepums/nap/ulsd
4Hafnia Sunda 1B 2/3 39.067 42.506 2015 Hamburg 21. Sep ulsd/ulsd/ulsdHafnia Sunda1B2/339.06742.5062015Hamburg21. Sepulsd/ulsd/ulsd
5Nordic Hanne S/B 2 38.395 43.444 2010 Turkey 18. Sep ulsd/ulsd/jetNordic HanneS/B238.39543.4442010Turkey18. Sepulsd/ulsd/jet
6Sikinos 1A 3 37.620 41.614 2006 Eleusis 20. Sep fo/fo/foSikinos1A337.6241.6142006Eleusis20. Sepfo/fo/fo
7Alice S 2/3 39.316 43.871 2013 Gaeta 20. Sep Sternline ulsd/ulsd/umsAliceS2/339.31643.8712013Gaeta20. SepSternline ulsd/ulsd/ums
8Seameridian B 49.999 56.845 2011 Singapore 18. Sep jet/ulsd/umsSeameridianB49.99956.8452011Singapore18. Sepjet/ulsd/ums
9MP MR Tanker 1 2/3 51.745 54.250 2011 if Kawasaki 24. Sep Naphtha/go/umsMP MR Tanker 12/351.74554.252011if Kawasaki24. SepNaphtha/go/ums
Sheet3
 
Upvote 0
If your sample data is representative, then I think a formula approach is feasible. You could try each of these, copied down.

Excel Workbook
ABCDEFGHIJKL
1Vessel Name ICE IMO DWT CBM Built Open DTD Last 3 cargoesVessel NameStatusICEIMODWTCBMBuiltOpenDTDLast 3 cargoes
2Hafnia Nordica B 49.999 59.100 2010 ARA for orders ex New York 20. Sep UKC 20. Sep ums/nap/jetHafnia NordicaB  49.99959.12010ARA for orders ex New York 20. Sep UKC20. Sepums/nap/jet
3Hafnia Lotte B 2/3 49.999 53.448 2017 ARA for orders ex Las Palmas 21. Sep ums/nap/ulsdHafnia LotteB2/349.99953.4482017ARA for orders ex Las Palmas21. Sepums/nap/ulsd
4Hafnia Sunda 1B 2/3 39.067 42.506 2015 Hamburg 21. Sep ulsd/ulsd/ulsdHafnia Sunda1B2/339.06742.5062015Hamburg21. Sepulsd/ulsd/ulsd
5Nordic Hanne S/B 2 38.395 43.444 2010 Turkey 18. Sep ulsd/ulsd/jetNordic HanneS/B238.39543.4442010Turkey18. Sepulsd/ulsd/jet
6Sikinos 1A 3 37.620 41.614 2006 Eleusis 20. Sep fo/fo/foSikinos1A337.6241.6142006Eleusis20. Sepfo/fo/fo
7Alice S 2/3 39.316 43.871 2013 Gaeta 20. Sep Sternline ulsd/ulsd/umsAliceS2/339.31643.8712013Gaeta 20. Sep Sternline ulsd/ulsd/ums20. Sepulsd/ulsd/ums
8Seameridian B 49.999 56.845 2011 Singapore 18. Sep jet/ulsd/umsSeameridianB49.99956.8452011Singapore18. Sepjet/ulsd/ums
9MP MR Tanker 1 2/3 51.745 54.250 2011 if Kawasaki 24. Sep Naphtha/go/umsMP MR Tanker 12/351.74554.252011if Kawasaki24. SepNaphtha/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
 
Upvote 0
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
 
Upvote 0
What version of Excel are you using & what operating system?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Finding numbers in cell with text
2
Replies
10
Views
268
Lancer7
L
S
  • Question Question
Split multiple groups of numbers with decimal points from a string
Replies
9
Views
318
SamJoy
S
J
  • Question Question
How to remove variably placed text within a string of text by looking at the columns to the right
Replies
1
Views
136
Alex Blakenburg
A
E
  • Solved
Formula for extracting multiple occurrences of text substring in a cell
2
Replies
19
Views
1K
Sektor
Sektor
T
  • Solved
How to split text and numbers in excel
Replies
4
Views
967
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top