Searching multi-line text strings

[goels]

Hi All,

I have cells with multi-line text strings. So for eg in one cell I have a 8 line text string:

Type\New
Payments\Twenty
Payments\Monthly
Payments\Ten
Payments\Five
Location\Chicago
In BBT
In RGT

Now each of these lines stores a different piece of information, for eg, 3rd line says person makes monthly payments, 6th line says person is located in Chicago and so on. The group of words in each line before the backslash are thus the main heading and the group of words after the backslash are sub-heading's.

Now I wanted to do a search saying that if the person's record has a particular main heading and then a particular sub-heading give me a value back which can either be a fixed number or the sub-heading itself. So for eg a query will be if the person's record has the main heading 'Payments' and then the sub-heading 'Monthly' give me the number 30 back. Another example will be if the person's record has the main heading 'Location' then give me the sub-heading 'Chicago' back.

Any ideas on how this can be acheived.

Thanks

[RichardWinter]

I'm not sure what you want to accomplish but storing multiple information in one text string doesn't seem like a sustainable concept.
In order to work with the data I would recommend to extract the information stored in the strings and put them into a proper database format (the part befor the slash as header and the values in cells corresponding to the person).

This you can achieve with a function like the one I provided in this thread: http://www.excelforum.com/excel-prog...tain-text.html

In this case it would be helpful too to provide an example workbook. By converting it to the 2003 format you will get a better chance for responses (some old fashioned guys like me still work in 2003).

[JBeaucaire]

Questions like this, it's not only important that your data problem be clear, you've explained that well, but also that we can actually see WHAT you're trying to accomplish as an end result. Any complex solution, like this may surely be, is greatly affected by the physical layout of the data rows AND the physical layout of the Result Rows.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Show us manually what you're doing, show several examples, perhaps we can help automate it without you having to split out the data. Perhaps not.