Concatenating and Unconcatenating

[J C]

I have a spreadsheet which contains info imported from another system. I
want to give users the chance to add records but they must follow the rules
so that the data remains consistent.

The problem I have is that there are 4 fields that I would like to have
completed from Picklists. The actual contents will vary depending on what is
downloaded from the other system and only certain combinations are allowed.
I have a list of all allowed combinations.

What I would like to do is concatenate the 4 fields so that the user only
has to pick once and then in different cells undo the concatenation so that
the four fields are presented in four cells. Unfortunately the contents of
the original four fields vary in length and even the number of words they
contain.

An example would be "Dog" "Four Legs" "Brown" "Fido". I want to be able to
combine all four, get "Dog Four Legs Brown Fido" put the result in a pick
list but when picked end up with a result that I can divide up to match the
original four fields.

Using Excel XP.

Any ideas?

John C

 

[Pete_UK]

When you concatenate the fields, put some character between them that
would not normally be part of the text, eg:

=A1&"|"&B1&"|"&C1&"|"&D1, or
=A1&":"&B1&":"&C1&":"&D1, or
=A1&"/"&B1&"/"&C1&"/"&D1

Then, to unconcatenate you can use SEARCH to find your character in
conjunction with LEFT, MID and RIGHT.

Hope this helps.

Pete

 

[J C]

Pete

That was exactly what I needed.

Thanks for the help

John C

 

[Pete_UK]

You're welcome, John - thanks for feeding back.

Pete