Questions

Excel Query - automatic re-formatting.

+
0 Votes
Locked

Excel Query - automatic re-formatting.

andrew.porritt
I'm currently trying to re-format a small (1500ish record) database for a client. The issue is that one column contains information (answers recorded from tick boxes) that isn't really in a useable format and needs to be split into separate columns.

As an example where someone has ticked boxes 1, 2 but not 3 it is recorded in the cell as 12.

I'm trying to see if I can split this out into 3 separate columns (one for each tick box) with an "x" appearing where a tick has been recorded.

I have managed to get as far as getting a formula that will work where only 1 box has been ticked by using =IF(L2=1,"x"," ")in each of the collumns (obviously submitting L2=2 and L2=3 in the respective collumns)however I'm screwed if there is more than one entry in the collumn.

To cut to the chase, is there a way of making the formula work if a 1 is found in the cell regardless of whether it appears as part of 12 or 123, or is there a way of having the formula look for more than one possible response before returning the "x" ie a working variation =IF(L2=1 or 12 or 123,"x"," ")

By the way I realise that I could possibly do this without too much hassle by hand, but I'm expecting larger files to fix. So if it's possible I could do with learning how

Thanks in advance for any help, and I apologise if I've given anyone a migraine with my convoluted explanation

Cheers

Andy