Formula help

[Barto]

Hi I have a data column that shows a series of level 1 and level 2 tags. the data presents as L1 space and then the name of the level 1 tag then a comma space then L2 space and the name of the level 2 tag. See examples below

I urgently need a formula that returns the L1 tag name and a second formula that just gives me the level 2 tag

Note there are up to 8 level 1 tags and 92 level 2 tags.... below is a small sample




[TABLE="width: 334"]
<colgroup><col></colgroup><tbody>[TR]
[TD]L1 Billing, L2 Website or IVR Enquiry[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Website or IVR Enquiry[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 High Bill Enquiry[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 High Bill Enquiry[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 High Bill Enquiry[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Bill Copy request[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 High Bill Enquiry[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Website or IVR Enquiry[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 High Bill Enquiry[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 High Bill Enquiry[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Account Information Request[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Account Information Request[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 High Bill Enquiry[/TD]
[/TR]
[TR]
[TD]L1 Movements or New Connections, L2 Move In[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Account Information Request[/TD]
[/TR]
[TR]
[TD]L1 Movements or New Connections, L2 Move In, L2 Move Out[/TD]
[/TR]
[TR]
[TD]L1 General, L2 General Enquiry[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Account Information Request[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Bill Copy request[/TD]
[/TR]
[TR]
[TD]L1 General, L2 Power Outage[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 High Bill Enquiry[/TD]
[/TR]
[TR]
[TD]L1 General, L2 Account Information Request[/TD]
[/TR]
[TR]
[TD]L1 General, L2 Power Outage[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Refund[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Refund[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Refund[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Account Information Request[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Account Information Request[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Refund[/TD]
[/TR]
[TR]
[TD]L1 General, L2 Website or IVR Enquiry[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Refund[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Refund[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Refund[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Refund[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Refund[/TD]
[/TR]
[TR]
[TD]L1 General, L2 Website or IVR Enquiry[/TD]
[/TR]
[TR]
[TD]L1 General, L2 Website or IVR Enquiry[/TD]
[/TR]
[TR]
[TD]L1 General, L2 Website or IVR Enquiry[/TD]
[/TR]
[TR]
[TD]L1 General, L2 Website or IVR Enquiry[/TD]
[/TR]
[TR]
[TD]L1 General, L2 Website or IVR Enquiry[/TD]
[/TR]
[TR]
[TD]L1 General, L2 Website or IVR Enquiry[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 P2P request[/TD]
[/TR]
[TR]
[TD]L1 Billing, L2 Refund[/TD]
[/TR]
[TR]
[TD]L1 General, L2 General Enquiry[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Is this what you want?

Excel Workbook
	A	B	C
1	L1 Billing, L2 Website or IVR Enquiry	Billing	Website or IVR Enquiry
2	L1 Billing, L2 Another Enquiry	Billing	Another Enquiry
3	L1 Movements or New Connections, L2 High Bill Enquiry	Movements or New Connections	High Bill Enquiry
Sheet1

 

[Barto]

thanks that worked but now have issue because sometimes there will be more than one L2 tag such as the example below

L1 Movements or New Connections, L2 Move In, L2 Move Out

 

[Peter_SSs]

thanks that worked but now have issue because sometimes there will be more than one L2 tag such as the example below

L1 Movements or New Connections, L2 Move In, L2 Move Out

.. but you haven't told us how/where the results of that data should be presented.

.. and could there be 2 (or more) L1 values and/or 2 (or more) L2 values? If so, what should happen?

 

[Barto]

Yeah thats my error. Initially I was lead to believe the data should only have one L1 piece of data and one L2 but I was wrong... the the formulas provided above work perfectly if only one L2 value.

So the operations team are telling me ideally they want to capture all L2 tags. There will always be one L1 tag but potentially up to two L2 tags

 

[Peter_SSs]

There will always be one L1 tag but potentially up to two L2 tags

OK, that has clarified one of my questions but you still haven't addressed this one.

.. but you haven't told us how/where the results of that data should be presented.

That is, if there are 2 L2 tags, does the 2nd L2 tag get reported
- in column D in JoeMo's layout?
- included in the the same cell as the 1st L2 tag (eg cell C1 in JoeMo's layout)?
- somewhere else?
- you don't care where?