How to split column text to another column?

Finding and extracting a substring from a much larger string of text (excel / not using text to column)?

• I have extracted all possible specs from a website and put it into one cell. I COULD parse it out using text to column with ";" as the delimiter but then I will get scattered cells all over the place with no way of organizing it by category header. (This is just 1 cell of 700+ cells with lumped together specs, and they all have a different number of specifications, all listed as "spec type=actual spec;") ex: ;Processor Brand=AMD;Processor=AMD;Processor Speed=1GHz;...anywhere from 15 to 40 more of these in any given row what I have done is gotten all possible 168 specification types which I have listed in a single row as my header. I am trying to write a formula that says something along the lines of: C3 (header row) = "Processor Brand=" , D3 = "Processor=" , E3 = "Processor Speed=" etc B4 = ;Processor Brand=AMD;Processor=AMD;Processor Speed=1GHz;Battery Type=6-cell;Display Type=Widescreen Active Matrix TFT Color LCD LED Backlit;Screen Size (Measured Diagonally)=15.6;Cache Memory=1MB;System Memory (RAM)=3GB;Type of Memory (RAM)=DDR3 SDRAM; writing the formula in C4 would give me "AMD", and in D4 would give me "AMD", E4 "1GHz" etc I've only been able to remove the text in row 3 from the cells in column B using a combination of a FIND, LEN and MID formula, which does nothing for me.

• Answer:

  This formula is kinda painful, but I think it gets the job done: =LEFT(RIGHT($B4,LEN($B4)- (FIND(C$3,$B4)+LEN(C$3)-1)), FIND(";",RIGHT($B4,LEN($B4)- (FIND(C$3,$B4)+LEN(C$3)-1)))-1) So the main idea is to take the big string and keep everything to the Right of where your header is found and Left of the semicolon following the header. So here's specifically how the formula works: 1. Take your big long string (B4) and keep everything to the right of your header (C3): "RIGHT(big string, length of big string - (position where header starts + length of header))" 2. Now, find the semicolon delimiter within the result of step 1 to know where this field stops. This gives us the length of the string we are interested in. Note that the "Right(...)" statement is just copied and pasted into the second "Find(";",...)" statement: FIND(position of semicolon, string after header) 3. Now keep everything to the left of the semicolon's position: LEFT(string after header, position of next semicolon-1) Hopefully this makes sense. More importantly, hopefully this works! Also you should be able to just drag the formula across and down as you need.