Strinc to split in cells

TorrO

Board Regular
Joined
Feb 13, 2003
Messages
118
Office Version
  1. 2013
Platform
  1. Windows
My string is from my smarthouse:

7.3.2018 10:25:40 +? 71 EnergiUte Strøm sender <MÅLEVERDI> til Smart Home Controller (DP:85) 8,430 (Netzversorgt) 74 %
7.3.2018 10:25:40 +0.0947 Smart Home Controller sender <BEKREFTELSE> til 71 EnergiUte (Netzversorgt) 58 %
7.3.2018 10:25:52 +12 Sek. 71 EnergiUte Spenning sender <MÅLEVERDI> til Smart Home Controller (DP:86) 221,39 (Netzversorgt) 76 %
7.3.2018 10:25:53 +0.0955 Smart Home Controller sender <BEKREFTELSE> til 71 EnergiUte (Netzversorgt) 54 %
7.3.2018 10:25:57 +4.3747 36Kaffimaskin (Effekt) sender <MÅLEVERDI> til Smart Home Controller (DP:12) 1,3 ( 36Kaffimaskin => 59Matbod ) (Netzversorgt) 66 %
7.3.2018 10:25:57 +0.0898 59Matbod sender <BEKREFTELSE> til 36Kaffimaskin (Netzversorgt) 80 %
7.3.2018 10:25:57 +0.0915 36Kaffimaskin (Effekt) sender <MÅLEVERDI> til Smart Home Controller (DP:12) 1,3 ( 59Matbod => Smart Home Controller ) (Netzversorgt) 80 %
7.3.2018 10:25:57 +0.0899 Smart Home Controller sender <BEKREFTELSE> til 59Matbod (Netzversorgt) 70 %
7.3.2018 10:26:12 +15 Sek. 71 EnergiUte Effekt sender <MÅLEVERDI> til Smart Home Controller (DP:84) 2028,0 (Netzversorgt) 78 %


Sample output in cell A to R:

A:7.3.2018 B:10:26:12 C:+15 D:Sek. E:71 F:EnergiUte G:Effekt H:sender I:<MÅLEVERDI> J:til K:Smart L:Home M:Controller N:(DP:84) O:2028,0 P:(Netzversorgt) Q:78 R:%


Can somebody help me with a formula/macro that splitt my string?

Thanks!
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
This will seperate at each space character the string in A1:

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(COLUMNS($A$1:A1)-1)*LEN($A1)+1,LEN($A1)))

Drag across until you get blank cells.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

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