Greetings everyone,
I have a calculation tool for Minecraft items that lists data from minecraft.fandom.com/wiki/Crafting and does various things with that data. I cannot get the queried data to spill properly because I don't know how.
I have a query to minecraft.fandom.com/wiki/Crafting/Bulding_blocks (to name one) that excludes the "Crafting recipe" column and the "Description column" (in Power Query Editor), on a sheet called Crafting & Ingredients Query. The data's listings sometimes show a choice of materials in the game by using an " or ". The cells display this with character 10 from the website.
I need a spill formula to separate and then continue the spill down for each occurrence of the " or ". I also need to show the same "Ingredient" on the right of those separations.
On a test sheet in range $B$4, I have a spill formula that simply references and filters the blanks of the Name column from the query on the Crafting & Ingredients Query sheet ('Crafting & Ingredients Query'!$B$5:$B$2000). In in range $D$4 of the test sheet, I am attempting to make a spill formula that will 1), remove all instances of " or " (CHAR(160)&"or"&CHAR(10)) then 2), display each item after the cell's delimiter ("or") sequentially underneath in the spill, and then 3) display the same Ingredient next to each item of the items that are transposed down.
Spill Formula so far (separates each item by the delimiter of Character 160, up to 10 delimiters for now):
Current Formula (spill from range $D$4 - incomplete)
=MID(SUBSTITUTE($B4:$B2000,CHAR(160),REPT(" ",LEN($B4:$B2000))),(TRANSPOSE(SEQUENCE(10,1)-1))*LEN($B4:$B2000)+1,LEN($B4:$B2000))
(Original formula credit: https://exceljet.net/formula/split-text-with-delimiter)
Notes:
• The =SEQUENCE() function is not dynamic (is just 10) and is temporary. Once each cell that has a delimiter is displayed underneath, the spill should be only 1 column wide.
• I want the number of delimiters a cell can have to be dynamic (no limit - infinite, 1 or 7 or 100 or whatever).
• Attached is the file with the relevant data.
I would be very grateful for any solution to make this spill formula happen. Thank you!
Thanks for the reply! XML is foreign to me so I have no idea how that formula works but it is surely a cleaner approach. Two things now to figure out and I can make it perfect.
1) May you make that spill?
2) Can you move everything else down from the split?
It doesn't matter what order they're in but I was thinking of maybe having a spill range of everything that doesn't have a delimiter and a spill range of everything that does (then I'll use =LET() to combine them later)?
That is exactly what I needed! Your genius formula splits text in a single cell by a delimiter ("or" in my case) and dynamically transposes it into a running spill array (even starts at the first string in the cell and inserts it where it appeared in the range - not all condensed at the top/bottom or something).
Thank you so much! You're a gentleman and a scholar. Best of luck to you in all your Excel endeavors.
Do you mean like this?
if so you mind trying an online tool?
Name
Nether Bricks
Red Nether Bricks
Chiseled Nether Bricks
id Name
1 Nether Bricks
2 Red Nether Bricks
3 Chiseled Nether Bricks
4 Block of Quartz
5 Chiseled Quartz Block
6 Quartz Pillar
7 Quartz Bricks
8 Hay Bale
9 Dyed Carpet
10 Dyed Terracotta
11 Packed Ice
12 Granite
13 Polished Granite
14 Sea Lantern
15 Coarse Dirt
16 Andesite
17 Polished Andesite
18 Prismarine
19 Prismarine Bricks
20 Dark Prismarine
21 Dark Prismarine
22 Magma Block
23 End Stone Bricks
24 Bone Block
25 Nether Wart Block
26 Purpur Block
27 Purpur Pillar
28 Concrete Powder
29 White Concrete Powder
29 Blue Concrete Powder
29 Brown Concrete Powder
29 Black Concrete Powder
30 Conduit
31 Dried Kelp Block
32 Wood
32 Hyphae
33 Stripped Wood
33 Stripped Hyphae
34 Block of Emerald
35 Block of Coal
36 Polished Blackstone Bricks
37 Chain
38 Block of Netherite
39 Polished Blackstone
40 Chiseled Polished Blackstone
41 Block of Gold
42 Stained Glass
43 Wood Stairs
44 Stone Stairs
45 Cobblestone Stairs
45 Mossy Cobblestone Stairs
46 Stone Brick Stairs
46 Mossy Stone Brick Stairs
47 Andesite Stairs
47 Polished Andesite Stairs
48 Diorite Stairs
48 Polished Diorite Stairs
49 Granite Stairs
49 Polished Granite Stairs
50 Sandstone Stairs
50 Red Sandstone Stairs
51 Smooth Sandstone Stairs
51 Smooth Red Sandstone Stairs
52 Brick Stairs
53 Prismarine Stairs
53 Prismarine Brick Stairs
53 Dark Prismarine Stairs
54 Nether Brick Stairs
54 Red Nether Brick Stairs
55 Quartz Stairs
56 Smooth Quartz Stairs
57 Purpur Stairs
58 End Stone Brick Stairs
59 Blackstone Stairs
59 Polished Blackstone Stairs
59 Polished Blackstone Brick Stairs
60 Cut Copper Stairs
60 Exposed Cut Copper Stairs
60 Weathered Cut Copper Stairs
60 Oxidized Cut Copper Stairs
61 Waxed Cut Copper Stairs
61 Waxed Exposed Cut Copper Stairs
61 Waxed Weathered Cut Copper Stairs
61 Waxed Oxidized Cut Copper Stairs
62 Waxed Cut Copper Stairs
62 Waxed Exposed Cut Copper Stairs
62 Waxed Weathered Cut Copper Stairs
62 Waxed Oxidized Cut Copper Stairs
63 Cobbled Deepslate Stairs
63 Polished Deepslate Stairs
63 Deepslate Brick Stairs
63 Deepslate Tile Stairs
64 Mud Brick Stairs
65 Bricks
66 Bookshelf
67 Snow Block
68 Clay
69 Jack o'Lantern
70 Glowstone
71 Block of Lapis Lazuli
72 Sandstone
72 Red Sandstone
73 Cut Sandstone
73 Cut Red Sandstone
74 Chiseled Sandstone
74 Chiseled Red Sandstone
75 Wood Slab
76 Stone Slab
76 Smooth Stone Slab
77 Cobblestone Slab
77 Mossy Cobblestone Slab
78 Stone Brick Slab
78 Mossy Stone Brick Slab
79 Andesite Slab
79 Polished Andesite Slab
80 Diorite Slab
80 Polished Diorite Slab
81 Granite Slab
81 Polished Granite Slab
82 Sandstone Slab
82 Red Sandstone Slab
83 Cut Sandstone Slab
83 Cut Red Sandstone Slab
84 Smooth Sandstone Slab
84 Smooth Red Sandstone Slab
85 Brick Slab
86 Prismarine Slab
86 Prismarine Brick Slab
86 Dark Prismarine Slab
87 Nether Brick Slab
87 Red Nether Brick Slab
88 Quartz Slab
89 Smooth Quartz Slab
90 Purpur Slab
91 Purpur Slab
92 End Stone Brick Slab
93 Blackstone Slab
93 Polished Blackstone Slab
93 Polished Blackstone Brick Slab
94 Cut Copper Slab
94 Exposed Cut Copper Slab
94 Weathered Cut Copper Slab
94 Oxidized Cut Copper Slab
95 Waxed Cut Copper Slab
95 Waxed Exposed Cut Copper Slab
95 Waxed Weathered Cut Copper Slab
95 Waxed Oxidized Cut Copper Slab
96 Waxed Cut Copper Slab
96 Waxed Exposed Cut Copper Slab
96 Waxed Weathered Cut Copper Slab
96 Waxed Oxidized Cut Copper Slab
97 Cobbled Deepslate Slab
97 Polished Deepslate Slab
97 Deepslate Brick Slab
97 Deepslate Tile Slab
98 Mud Brick Slab
99 Melon
100 Stone Bricks
101 Mossy Stone Bricks
102 Mossy Stone Bricks
103 Chiseled Stone Bricks
104 Block of Iron
105 Tinted Glass
106 Block of Amethyst
107 White Wool
108 Dyed Wool
109 Wool
110 White Wool
111 Scaffolding
112 Dripstone Block
113 Overworld Planks
114 Planks
115 Blue Ice
116 Polished Deepslate
117 Chiseled Deepslate
118 Diorite
119 Polished Diorite
120 Deepslate Tiles
121 Deepslate Bricks
122 Block of Copper
123 Cut Copper
123 Exposed Cut Copper
123 Weathered Cut Copper
123 Oxidized Cut Copper
124 Waxed Block of Copper
124 Waxed Exposed Copper
124 Waxed Weathered Copper
124 Waxed Oxidized Copper
124 Waxed Cut Copper
124 Waxed Exposed Cut Copper
124 Waxed Weathered Cut Copper
124 Waxed Oxidized Cut Copper
125 Waxed Cut Copper
125 Waxed Exposed Cut Copper
125 Waxed Weathered Cut Copper
125 Waxed Oxidized Cut Copper
126 Block of Raw Gold
127 Block of Raw Iron
128 Block of Diamond
129 Mossy Cobblestone
130 Mossy Cobblestone
131 Hardened Glass
132 Hardened Stained Glass
133 Block of Raw Copper
134 Mud Bricks
135 Mud Brick Slab
136 Mud Brick Stairs
137 Mud Brick Wall
Accepted Solutions