J
Joannie Jae
I've been given the task of creating a new database from an old one
where multiple things were typed into one field (a very large file,
and a huge mess). Let's assume column A is the data, which looks
something like this:
A
1 Radio grid A4 Fire zone Alpha Network 4C
2 fire bravo radio C9 network 4D
3 radio A2 fire delta net 4C
4 network 4F Fire zone Charlie radio A9
5 Network 4P radio A2 fire zone delta
I need to split this data into three columns, Radio Grid, Fire Zone,
and Network (B, C, and D respectively). Knowing that the items have
unique zones (only the Fire Zone will contain alpha, bravo, charlie,
delta, etcetera), how can I create a formula that looks for incidents
of each word and assigns the correct term to the matching location in
C?
It should wind up looking like this, after all the data is split:
A B C D
1 Radio grid A4 Fire zone Alpha Network 4C A4 Alpha 4C
2 fire bravo radio C9 network 4D C9 Bravo 4D
3 radio A2 fire Delta net 4C A2 Delta 4C
4 network 4F Fire zone Charlie radio A9 A9 Charlie 4F
5 Network 4P radio A2 fire zone delta A2 Delta 4P
If the word is not found at all, I would like for it to leave the cell
blank, so I can go back and enter it by hand. (Some of the data
doesn't have all three items.) Is there a formula that can pull a
trick like this? I've been toying with it for several days now, and
come up with nothing so far. I've been so frustrated, I've been
tempted to drop the whole thing into QBasic and pull some massive
programming tricks out of my repertoire. ;-)
Any help would be most appreciated!
Joannie
where multiple things were typed into one field (a very large file,
and a huge mess). Let's assume column A is the data, which looks
something like this:
A
1 Radio grid A4 Fire zone Alpha Network 4C
2 fire bravo radio C9 network 4D
3 radio A2 fire delta net 4C
4 network 4F Fire zone Charlie radio A9
5 Network 4P radio A2 fire zone delta
I need to split this data into three columns, Radio Grid, Fire Zone,
and Network (B, C, and D respectively). Knowing that the items have
unique zones (only the Fire Zone will contain alpha, bravo, charlie,
delta, etcetera), how can I create a formula that looks for incidents
of each word and assigns the correct term to the matching location in
C?
It should wind up looking like this, after all the data is split:
A B C D
1 Radio grid A4 Fire zone Alpha Network 4C A4 Alpha 4C
2 fire bravo radio C9 network 4D C9 Bravo 4D
3 radio A2 fire Delta net 4C A2 Delta 4C
4 network 4F Fire zone Charlie radio A9 A9 Charlie 4F
5 Network 4P radio A2 fire zone delta A2 Delta 4P
If the word is not found at all, I would like for it to leave the cell
blank, so I can go back and enter it by hand. (Some of the data
doesn't have all three items.) Is there a formula that can pull a
trick like this? I've been toying with it for several days now, and
come up with nothing so far. I've been so frustrated, I've been
tempted to drop the whole thing into QBasic and pull some massive
programming tricks out of my repertoire. ;-)
Any help would be most appreciated!
Joannie