cleaning/formatting data

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
something like this?

day & hourHst n x (m) - -Hs Tp dir (m) (s) (d)Hs Tp dir (m) (s) (d)_1Hs Tp dir (m) (s) (d)_2Hs Tp dir (m) (s) (d)_3Hs Tp dir (m) (s) (d)_4Hs Tp dir (m) (s) (d)_5
6 154.74 14.74 12.3 67
6 164.72 14.72 12.0 68
6 174.73 14.73 11.8 69
6 184.75 14.75 11.7 70
6 194.78 14.78 11.7 70
6 204.82 14.82 11.6 70
6 214.88 14.88 11.5 71
6 224.96 14.96 11.5 71
6 235.06 25.01 11.6 720.73 10.3 349
7 05.16 15.16 11.6 71
7 15.25 15.25 11.8 70
7 25.32 15.32 12.0 70
7 35.38 15.38 12.2 69
7 45.44 15.44 12.5 69
7 55.51 15.51 12.7 68
7 65.57 15.57 12.9 67
7 75.64 15.64 13.3 66
7 85.72 15.72 13.8 66
7 95.79 15.79 14.2 65
7 105.83 15.83 14.5 65
7 115.82 15.82 14.7 64
7 125.77 25.77 15.1 640.26 9.6 339
7 135.69 15.69 15.4 64
7 145.57 15.57 15.3 64
7 155.43 15.43 15.3 64
7 165.28 15.28 15.2 64
7 175.13 15.13 15.1 65
7 184.98 14.98 14.9 66
7 194.84 14.84 14.7 67
7 204.70 14.70 14.5 68
7 214.56 14.56 14.4 69
7 224.44 14.44 14.3 70
7 234.32 14.32 14.2 71
8 04.23 14.23 14.1 72
8 14.15 14.15 13.9 73
8 24.09 14.09 13.8 75
8 34.05 14.05 13.6 76
8 44.03 14.03 13.5 78
8 54.03 14.03 13.4 79
8 64.04 14.04 13.3 81
8 74.05 14.05 13.2 81
8 84.03 23.96 13.1 830.80 16.6 55
8 94.02 23.95 12.9 830.73 16.8 52
8 104.01 23.94 12.8 830.79 16.6 52
8 114.03 23.95 12.8 830.80 16.5 52
8 124.06 23.98 12.8 840.82 16.5 53
8 134.09 23.99 12.8 840.88 16.5 55
8 144.10 14.10 12.8 82
8 154.11 14.11 12.8 82
8 164.11 23.96 12.9 841.13 15.9 58
8 174.12 14.12 13.0 82
8 184.12 14.12 13.2 81
8 194.12 14.12 13.5 81
8 204.11 14.11 13.9 81
8 214.09 14.09 14.1 81
8 224.05 14.05 14.1 81
8 233.99 13.99 14.2 81
9 03.91 13.91 14.1 81
9 13.83 13.83 14.1 81
9 23.73 13.73 14.0 81
9 33.63 13.63 13.9 81
9 43.54 13.54 13.8 80
9 53.44 23.13 13.2 881.44 14.0 44
9 63.35 22.92 13.3 901.64 13.9 48
9 73.27 22.76 13.1 911.76 14.4 50
9 83.20 22.63 13.2 931.82 14.3 51
9 93.14 22.59 12.8 931.78 14.1 48
9 103.09 22.45 12.8 941.89 13.8 49
9 113.05 22.33 12.7 961.96 13.4 49
9 123.01 22.21 12.6 982.04 13.3 50
9 132.97 22.12 12.5 992.08 13.1 49
9 142.93 22.07 12.2 992.08 12.9 49
9 152.90 31.91 12.1 972.11 12.9 50* 0.55 3.4 208
9 162.87 31.86 12.0 982.08 12.7 49* 0.68 3.6 209
9 172.86 31.82 12.0 972.02 12.5 47* 0.87 3.9 213
9 182.86 31.72 12.3 982.00 12.3 48* 1.10 4.4 217
9 192.85 31.64 12.2 991.95 12.1 47* 1.28 4.8 217
9 202.85 31.51 11.8 1001.94 12.1 49* 1.43 5.1 215
9 212.85 31.43 11.9 1011.89 11.8 49* 1.58 5.4 214
9 222.82 41.41 11.7 1011.82 11.8 48* 1.62 5.7 2140.18 22.8 69
9 232.77 41.43 11.6 1011.74 11.6 47* 1.60 6.0 2150.23 22.6 69
10 02.74 51.50 11.2 1011.65 11.6 46* 1.56 6.3 2170.28 22.2 690.15 12.7 338
10 12.79 51.56 11.0 1021.59 11.5 461.64 6.8 2110.34 21.9 700.16 12.7 337
10 22.99 5 11.57 11.2 470.41 21.6 700.17 12.6 336* 2.15 10.9 123
10 33.57 51.25 7.3 2261.49 11.2 470.48 21.5 710.18 12.6 337* 2.95 10.8 115
10 44.16 51.15 7.7 2261.46 10.7 48* 3.67 9.1 1130.56 21.2 710.21 12.4 338
10 54.53 51.08 7.9 226* 4.14 9.6 1110.65 20.8 710.22 12.5 3341.33 12.3 46
10 64.68 51.02 8.1 225* 4.28 9.9 1100.74 20.4 720.25 12.3 3341.39 12.5 49
10 74.69 50.97 8.3 2251.30 10.9 48* 4.31 10.2 1090.85 20.2 710.27 12.1 336
10 84.62 50.92 8.4 2241.25 12.0 48* 4.24 10.4 1090.94 19.9 720.27 12.0 335
10 94.53 50.88 8.4 2231.20 12.1 48* 4.13 10.5 1091.04 19.5 720.29 11.9 335
10 104.51 50.83 8.5 2221.16 12.2 48* 4.11 10.6 1091.16 19.1 710.30 11.9 335
10 114.60 50.77 8.5 2211.13 12.3 49* 4.20 10.7 1081.25 18.8 710.33 11.8 338
10 124.79 50.70 8.5 2201.07 12.3 49* 4.40 10.7 1081.35 18.5 710.33 11.8 336
10 134.90 50.63 8.5 2191.06 12.3 51* 4.51 10.8 1081.42 18.1 710.32 11.7 336
10 144.99 50.58 8.5 2191.00 12.4 50* 4.61 10.8 1071.48 17.7 710.32 11.6 335
10 155.08 50.53 8.4 2180.91 12.4 49* 4.71 10.9 1071.56 17.5 710.32 11.5 335
10 165.22 50.48 8.4 2180.78 12.3 52* 4.85 10.9 1071.66 17.2 710.33 11.4 336
10 175.33 40.46 8.3 217* 5.02 11.0 1051.72 16.7 700.33 11.4 336
10 185.43 40.43 8.3 217* 5.05 10.9 1051.91 16.4 680.33 11.3 337
10 195.45 30.42 8.5 216* 5.43 11.1 990.33 11.3 337
10 205.44 30.41 8.7 216* 5.41 11.1 970.32 11.2 337
10 215.41 30.40 9.0 215* 5.38 11.1 950.31 11.2 336
10 225.36 30.39 9.2 214* 5.34 11.1 940.31 11.2 336
10 235.34 30.37 9.4 214* 5.31 11.0 930.31 11.1 336
11 05.31 30.35 9.4 213* 5.29 10.9 930.30 11.0 336
11 15.38 30.32 9.4 212* 5.36 10.8 930.30 11.0 336
11 25.50 30.28 9.3 211* 5.48 10.6 940.29 10.9 336
11 35.68 30.24 9.2 210* 5.67 10.6 950.28 10.8 336
11 45.68 30.21 9.1 210* 5.67 10.6 950.28 10.8 336
11 55.50 30.18 9.0 209* 5.49 10.7 930.27 10.7 336
11 65.24 2* 5.23 10.7 910.27 10.7 336
11 74.92 3* 4.91 12.5 890.15 24.5 470.27 10.6 336
11 84.64 44.39 12.4 860.18 23.8 471.47 14.7 990.27 10.6 336
11 94.41 44.14 12.5 840.21 23.5 471.47 14.7 980.27 10.5 335
11 104.24 34.22 13.2 850.23 23.4 480.27 10.5 335
11 114.10 50.25 23.4 483.23 13.4 1032.00 9.2 470.26 10.5 3351.50 13.2 58
11 123.98 40.27 23.3 483.06 13.4 1030.26 10.4 3352.51 13.0 54
11 133.86 40.29 23.2 483.00 13.4 1020.26 10.4 3352.40 12.9 53
11 143.74 40.31 23.1 482.88 13.3 1020.26 10.3 3352.35 12.8 53
11 153.62 40.34 22.7 482.84 13.3 1010.26 10.3 3352.21 12.7 50
11 163.51 40.38 22.1 482.72 13.2 1020.26 10.2 3352.17 12.7 50
11 173.40 40.42 21.9 482.58 13.2 1020.26 10.2 3352.15 12.6 51
11 183.30 40.46 21.8 482.56 13.0 1010.26 10.1 3352.02 12.5 49
11 193.22 40.50 21.8 492.46 12.9 1010.25 10.0 3342.00 12.4 48
11 203.15 40.52 21.7 492.32 12.7 1020.25 9.9 3332.06 12.3 50
11 213.11 40.55 21.6 492.27 12.6 1010.25 9.8 3332.03 12.1 47
11 223.07 40.57 21.5 492.17 12.5 1020.24 9.7 3392.07 11.5 47
11 233.03 40.61 21.1 492.11 12.5 1010.23 9.6 3392.08 11.2 46
12 03.00 40.65 20.6 492.04 12.4 1020.23 9.5 3382.09 10.7 45
12 12.97 5* 0.50 2.8 2150.70 20.5 491.97 12.4 1010.27 9.4 3432.02 10.6 43
12 22.94 5* 0.65 3.3 2150.75 20.4 491.89 12.3 1010.18 18.2 122.00 10.5 41
12 32.91 5* 0.83 3.7 2130.80 20.3 491.81 12.2 1010.25 17.8 121.95 10.4 41
12 42.91 5* 1.03 4.1 2130.85 20.2 501.74 12.0 1010.34 17.4 121.87 10.3 39
12 52.93 5* 1.27 4.6 2130.88 20.1 501.67 11.9 1010.45 17.0 131.79 10.2 38
12 62.99 5* 1.54 5.0 2130.90 19.9 501.60 11.8 1010.56 16.6 131.69 10.0 36
12 73.09 5* 1.83 5.5 2130.93 19.6 501.52 11.8 1010.69 16.2 131.60 9.8 35
12 83.25 6* 2.14 5.9 2130.97 19.3 501.44 11.7 1010.82 15.8 130.17 18.1 861.49 9.7 34
12 93.46 6 1* 2.49 6.4 2131.00 19.1 501.36 11.7 1010.95 15.5 141.40 9.6 33
12 103.59 6* 2.69 6.8 2131.04 19.0 501.29 11.6 1011.07 15.3 140.20 18.0 881.32 9.4 32
12 113.65 7 2* 2.77 7.1 2121.08 18.8 501.23 11.6 1011.18 14.9 140.23 17.5 87
12 123.64 8 2* 2.75 7.3 2121.11 18.7 501.17 11.6 1011.29 14.6 140.25 17.4 870.88 11.4 47
12 133.52 8 22.58 7.4 2121.14 18.6 501.13 11.5 1001.38 14.4 140.27 17.3 870.81 11.3 48
12 143.40 62.35 7.5 2131.18 18.5 501.18 11.4 1031.58 14.1 120.30 17.1 850.81 11.3 50
12 153.38 6 12.15 7.7 2161.19 18.4 501.45 11.1 1091.63 13.8 130.31 17.0 87
12 163.46 61.91 7.8 2241.21 18.1 501.89 11.0 1151.65 13.6 140.32 16.7 850.69 13.1 52
12 173.54 61.77 7.9 2271.23 17.9 502.17 10.9 1171.63 13.4 140.30 15.9 890.68 13.0 53
12 183.62 61.63 8.1 2291.22 17.7 502.41 10.8 1191.60 13.2 150.29 16.5 870.68 12.9 54
12 193.72 61.53 8.3 2301.25 17.6 512.65 10.6 1211.59 13.0 170.24 15.8 900.55 12.9 56
12 203.93 61.44 8.4 2291.23 17.5 51* 3.03 10.4 1221.51 12.8 170.28 16.3 870.58 12.9 54
12 214.29 51.35 8.5 2291.25 17.4 50* 3.56 10.4 1211.48 12.8 190.39 12.3 58
12 224.64 51.29 8.6 2281.27 17.3 51* 4.01 10.5 1201.40 12.7 200.39 12.3 57
12 234.88 51.20 8.6 2271.26 17.3 51* 4.34 10.8 1181.30 12.5 190.48 12.6 55
13 05.04 51.11 8.6 2271.26 17.2 51* 4.56 10.9 1181.23 12.3 200.48 12.6 54
13 15.11 51.06 8.6 2271.25 17.2 51* 4.67 11.1 1161.17 12.2 200.50 12.6 55
13 25.14 40.99 8.6 2271.35 16.8 51* 4.73 11.2 1151.10 11.9 21
13 35.12 40.95 8.6 2261.36 16.4 52* 4.74 11.3 1141.02 11.9 20
13 45.07 40.90 8.6 2251.37 16.3 52* 4.70 11.4 1130.95 11.9 19
13 54.99 40.86 8.5 2241.34 16.3 52* 4.64 11.5 1120.91 11.8 20
13 64.90 40.82 8.5 2241.35 16.3 52* 4.57 11.5 1110.85 11.7 20
13 74.84 40.78 8.4 2231.34 16.2 52* 4.51 11.6 1100.82 11.6 20
13 84.80 40.74 8.4 2231.34 16.2 52* 4.48 11.6 1100.80 11.6 21
13 94.81 40.72 8.4 2221.37 16.2 524.50 11.7 1090.74 11.6 21
13 104.94 50.69 8.4 2221.31 16.2 53* 4.63 11.7 1100.75 11.6 240.40 19.1 50
13 115.16 50.67 8.4 2211.33 16.2 53* 4.87 11.6 1100.68 11.4 210.47 18.9 48
13 125.48 40.64 8.4 2211.44 15.8 53* 5.21 11.6 1100.62 11.3 20
13 135.78 30.60 8.5 2211.56 16.2 48* 5.53 11.8 109
13 146.06 30.57 8.6 2211.41 14.9 46* 5.87 11.9 108
13 156.31 40.54 8.6 221* 6.19 12.2 1051.04 16.8 500.22 22.0 50
13 166.51 40.52 8.7 221* 6.41 12.4 1030.96 17.2 520.24 21.9 49
13 176.60 40.53 8.8 220* 6.48 12.5 1001.10 17.2 520.33 21.2 50
13 186.66 20.53 8.8 220* 6.63 12.5 96
13 196.54 30.53 8.8 2190.38 20.7 50* 6.51 12.5 93
13 206.37 30.51 8.8 2190.41 20.6 50* 6.34 12.5 90
13 216.18 30.49 8.7 2180.43 20.5 50* 6.15 12.5 87
13 225.99 40.45 8.7 2170.45 20.5 50* 5.76 12.5 871.54 15.9 51
13 235.82 40.41 8.7 2170.48 20.5 505.61 12.5 851.38 16.2 52
14 05.64 40.37 8.6 2160.59 20.0 515.43 12.6 831.35 16.2 52
14 15.51 30.33 8.6 2165.29 12.6 821.50 16.2 51
14 25.42 30.29 8.5 2155.19 12.6 821.53 16.2 52
14 35.37 40.26 8.4 2160.79 19.1 525.08 12.6 821.51 15.9 51
14 45.38 30.82 19.1 525.03 12.6 831.71 15.7 53
14 55.46 30.91 18.8 515.09 12.7 831.75 15.4 52
14 65.58 30.94 18.8 52* 5.17 12.8 841.86 15.1 52
14 75.66 2* 5.25 12.8 832.11 15.5 51
14 85.70 15.70 13.1 78
14 95.72 15.72 13.1 77
14 105.78 15.78 13.0 78
14 115.94 15.94 13.0 79
14 126.16 20.93 18.3 576.09 12.9 80

Code:
[SIZE=1]// multi_1 STHSEC
let
    Source = Csv.Document(Web.Contents("https://polar.ncep.noaa.gov/waves/WEB/multi_1.latest_run/plots/multi_1.STHSEC.bull"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    RemTop = Table.Skip(Source,4),
    RemBottom = Table.RemoveLastN(RemTop,10),
    Replace = Table.ReplaceValue(RemBottom," +-------+-----------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+","",Replacer.ReplaceText,{"Column1"}),
    RC = Table.RemoveColumns(Replace,{"Column2"}),
    Split = Table.SplitColumn(RC, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10"}),
    Type = Table.TransformColumnTypes(Split,{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}}),
    RC2 = Table.RemoveColumns(Type,{"Column1.1", "Column1.10"}),
    Filter = Table.SelectRows(RC2, each ([Column1.4] <> null)),
    Trim = Table.TransformColumns(Filter,{{"Column1.2", Text.Trim, type text}, {"Column1.3", Text.Trim, type text}, {"Column1.4", Text.Trim, type text}, {"Column1.5", Text.Trim, type text}, {"Column1.6", Text.Trim, type text}, {"Column1.7", Text.Trim, type text}, {"Column1.8", Text.Trim, type text}, {"Column1.9", Text.Trim, type text}}),
    Clean = Table.TransformColumns(Trim,{{"Column1.2", Text.Clean, type text}, {"Column1.3", Text.Clean, type text}, {"Column1.4", Text.Clean, type text}, {"Column1.5", Text.Clean, type text}, {"Column1.6", Text.Clean, type text}, {"Column1.7", Text.Clean, type text}, {"Column1.8", Text.Clean, type text}, {"Column1.9", Text.Clean, type text}}),
    Transpose = Table.Transpose(Table.CombineColumns(Table.Transpose(Clean),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"header")),
    Promote = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true])
in
    Promote[/SIZE]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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