Hi, Hope someone can assist with this query.
I have an excel sheet with one cell that contains a long string of data. What I need to do is to extract certain values from that string and build a table of data.
The data i want to extract is wrapped in curly brackets. I only want to extract the last value of the string from each of the data wrapped in curly bracktes.
Below is samole data in Column A, rows 2,3 & 4
Expected Output
Based on row 2
Can this be achived easily?
Thanking you in advace.
I have an excel sheet with one cell that contains a long string of data. What I need to do is to extract certain values from that string and build a table of data.
The data i want to extract is wrapped in curly brackets. I only want to extract the last value of the string from each of the data wrapped in curly bracktes.
Below is samole data in Column A, rows 2,3 & 4
a:9:{i:0;a:2:{s:3:"key";s:9:"lead-form";s:5:"value";s:4:"2028";}i:1;a:2:{s:3:"key";s:10:"first-name";s:5:"value";s:3:"Mal";}i:2;a:2:{s:3:"key";s:9:"last-name";s:5:"value";s:7:"Spencer";}i:3;a:2:{s:3:"key";s:5:"email";s:5:"value";s:23:"test@gmail.com";}i:4;a:2:{s:3:"key";s:5:"phone";s:5:"value";s:10:"040000000";}i:5;a:2:{s:3:"key";s:5:"state";s:5:"value";s:8:"Victoria";}i:6;a:2:{s:3:"key";s:9:"post-code";s:5:"value";s:4:"3170";}i:7;a:2:{s:3:"key";s:4:"type";s:5:"value";s:11:"Residential";}i:8;a:2:{s:3:"key";s:7:"message";s:5:"value";s:203:"I am interested in purchasing half a dozen solar powered LED spot lights around my homes external walls. Please let me know what my options are and the cost range as I have someone who can install them.";}} |
a:8:{i:0;a:2:{s:3:"key";s:9:"lead-form";s:5:"value";s:4:"2026";}i:1;a:2:{s:3:"key";s:10:"first-name";s:5:"value";s:5:"Rafia";}i:2;a:2:{s:3:"key";s:9:"last-name";s:5:"value";s:6:"Sarwar";}i:3;a:2:{s:3:"key";s:5:"email";s:5:"value";s:22:"test975@gmail.com";}i:4;a:2:{s:3:"key";s:5:"phone";s:5:"value";s:10:"3092929499";}i:5;a:2:{s:3:"key";s:5:"state";s:5:"value";s:3:"NSW";}i:6;a:2:{s:3:"key";s:4:"type";s:5:"value";s:11:"Residential";}i:7;a:2:{s:3:"key";s:7:"message";s:5:"value";s:1:"0";}} |
a:13:{i:0;a:2:{s:3:"key";s:9:"lead-form";s:5:"value";s:4:"2030";}i:1;a:2:{s:3:"key";s:10:"first-name";s:5:"value";s:4:"Greg";}i:2;a:2:{s:3:"key";s:9:"last-name";s:5:"value";s:7:"Bristow";}i:3;a:2:{s:3:"key";s:5:"email";s:5:"value";s:21:"test123@gmail.com";}i:4;a:2:{s:3:"key";s:5:"phone";s:5:"value";s:10:"0400830017";}i:5;a:2:{s:3:"key";s:7:"address";s:5:"value";s:4:"3090";}i:6;a:2:{s:3:"key";s:10:"downlights";s:5:"value";s:2:"86";}i:7;a:2:{s:3:"key";s:9:"cfl-bulbs";s:5:"value";s:2:"12";}i:8;a:2:{s:3:"key";s:6:"a-bulb";s:5:"value";s:2:"12";}i:9;a:2:{s:3:"key";s:9:"reflector";s:5:"value";s:1:"0";}i:10;a:2:{s:3:"key";s:12:"candle-globe";s:5:"value";s:1:"6";}i:11;a:2:{s:3:"key";s:9:"best-time";s:5:"value";s:7:"Evening";}i:12;a:2:{s:3:"key";s:7:"how-did";s:5:"value";s:13:"Search Engine";}} |
Expected Output
Based on row 2
Lead-Form | First-name | last-name | phone | state | post code | type | message | |
2028 | Mal | Spencer | test@gmail.com | 040000000 | victoria | 3170 | Residential | I am interested in purchasing half a dozen solar powered LED spot lights around my homes external walls. Please let me know what my options are and the cost range as I have someone who can install them. |
Can this be achived easily?
Thanking you in advace.