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

L1 Billing, L2 Website or IVR Enquiry

L1 Billing, L2 Website or IVR Enquiry

L1 Billing, L2 High Bill Enquiry

L1 Billing, L2 High Bill Enquiry

L1 Billing, L2 High Bill Enquiry

L1 Billing, L2 Bill Copy request

L1 Billing, L2 High Bill Enquiry

L1 Billing, L2 Website or IVR Enquiry

L1 Billing, L2 High Bill Enquiry

L1 Billing, L2 High Bill Enquiry

L1 Billing, L2 Account Information Request

L1 Billing, L2 Account Information Request

L1 Billing, L2 High Bill Enquiry

L1 Movements or New Connections, L2 Move In

L1 Billing, L2 Account Information Request

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

L1 General, L2 General Enquiry

L1 Billing, L2 Account Information Request

L1 Billing, L2 Bill Copy request

L1 General, L2 Power Outage

L1 Billing, L2 High Bill Enquiry

L1 General, L2 Account Information Request

L1 General, L2 Power Outage

L1 Billing, L2 Refund

L1 Billing, L2 Refund

L1 Billing, L2 Refund

L1 Billing, L2 Account Information Request

L1 Billing, L2 Account Information Request

L1 Billing, L2 Refund

L1 General, L2 Website or IVR Enquiry

L1 Billing, L2 Refund

L1 Billing, L2 Refund

L1 Billing, L2 Refund

L1 Billing, L2 Refund

L1 Billing, L2 Refund

L1 General, L2 Website or IVR Enquiry

L1 General, L2 Website or IVR Enquiry

L1 General, L2 Website or IVR Enquiry

L1 General, L2 Website or IVR Enquiry

L1 General, L2 Website or IVR Enquiry

L1 General, L2 Website or IVR Enquiry

L1 Billing, L2 P2P request

L1 Billing, L2 Refund

L1 General, L2 General Enquiry

<colgroup><col></colgroup><tbody>
</tbody>

 

[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?