Split text and numbers in a text string

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]
 
I thought it was a bit cheeky to ask about Google Sheets on an Excel forum. I'm more pro Excel anyway. Thank you for all your help. I will continue trying to find solutions to this.
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi Peter, how are you?

I have another Text to Columns problem i want to solve. It's very similar to the last one but the column layout is different.

I want to split the follow text strings into columns. Below I have written the delimiters:

[TABLE="width: 572"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Alpine Persefone LR1 74,500 Sungdong 2008 13,500 Navig8 1 Year[/TD]
[/TR]
[TR]
[TD]Archangelos Gabriel LR1 72,300 Hudong 2005 14,000 Trafigura Beheer BV 9 Months[/TD]
[/TR]
[TR]
[TD]Alpine Penelope LR1 74,400 Sungdong 2008 14,500 Inc Options Vitol 1 Year[/TD]
[/TR]
[TR]
[TD]Nord Vantage MR2 50,600 Hyundai Vinashin 2018 16,000 Union Maritime 1 Year[/TD]
[/TR]
[TR]
[TD]Centennial Misumi MR2 47,200 Onomichi Dockyard 2008 13,250 Phillips 66 1 Year[/TD]
[/TR]
[TR]
[TD]Front Loki Suezmax 156,000 Jiangsu Rongsheng 2010 23,500 Repsol 1 Year

Example CSV
Vessel Name, Type, DWT, Yard, Built, Rate, Charterer, Period

Alpine Persefone, LR1, 74500, Sungdong, 2008, 13500, Navig8, 1 Year


Delimiters

Vessel Name stops when LR1 or MR2 or Suezmax or VLCC or LR2 or or MR1 or Handy appears.

DWT is always a number

Yard is always Text

Built is always a number

Rate is always a number

Charterer can be text and numbers

Period maybe days, month, months, year, years, min-max

Again I prefer a formula over a Macro

Many thanks for any help you can provide.

Kind regards
Bear 1

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi Peter, how are you?

I have another Text to Columns problem i want to solve. It's very similar to the last one but the column layout is different.

I want to split the follow text strings into columns. Below I have written the delimiters:

[TABLE="class: cms_table, width: 572"]
<tbody>[TR]
[TD]Alpine Persefone LR1 74,500 Sungdong 2008 13,500 Navig8 1 Year[/TD]
[/TR]
[TR]
[TD]Archangelos Gabriel LR1 72,300 Hudong 2005 14,000 Trafigura Beheer BV 9 Months[/TD]
[/TR]
[TR]
[TD]Alpine Penelope LR1 74,400 Sungdong 2008 14,500 Inc Options Vitol 1 Year[/TD]
[/TR]
[TR]
[TD]Nord Vantage MR2 50,600 Hyundai Vinashin 2018 16,000 Union Maritime 1 Year[/TD]
[/TR]
[TR]
[TD]Centennial Misumi MR2 47,200 Onomichi Dockyard 2008 13,250 Phillips 66 1 Year[/TD]
[/TR]
[TR]
[TD]Front Loki Suezmax 156,000 Jiangsu Rongsheng 2010 23,500 Repsol 1 Year

Example CSV
Vessel Name, Type, DWT, Yard, Built, Rate, Charterer, Period

Alpine Persefone, LR1, 74500, Sungdong, 2008, 13500, Navig8, 1 Year


Delimiters

Vessel Name stops when LR1 or MR2 or Suezmax or VLCC or LR2 or or MR1 or Handy appears.

DWT is always a number

Yard is always Text

Built is always a number

Rate is always a number

Charterer can be text and numbers

Period maybe days, month, months, year, years, min-max

Again I prefer a formula over a Macro

Many thanks for any help you can provide.

Kind regards
Bear 1[/TD]
[/TR]
</tbody>[/TABLE]

 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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