Function to Break Apart Fields

[cblades3]

Hello community,

So my issue is that I have a combined field coming from an excel file with hundreds of rows that looks something like this:

1. Lab Category A

a.  Lab abc

b. Lab xyz

2. Lab Category B

a. Lab lmn

b. Lab dfc

Basically I want to break apart this column and assign the lab category to the matching labs beneath it until I hit the next lab category like this:

 Lab abc          Lab Category A

 Lab xyz          Lab Category A

 Lab lmn         Lab Category B

 Lab dfc          Lab Category B

I have tried Regex Match to no avail thus far. Thank you for any assistance!

[ivoller]

Can you share sample data? I think you're on the right track but may need to use a Multi-field formula or similar to get the break down required.

[cblades3]

So my example is the sample data ,with names omitted, from a field in my spread sheet. The rest of the associated data to specific labs and their associated categories is quantitative. To elaborate though it would be something like this

Lab Type	NA - Lab Count	Data Consumption	Priority Upgrade
1. Body and Chassis	1	1	.9
a. Full Vehicle Test Sys	1	1	.65
b. Component Sys	1	1	.20
c. Some Other Sys	1	1	.4
d. Another System	1	1	.1
2. Electrical	1	1	.15
a. Harness	1	1	.8

The real issue is splitting up the Lab column and assigning the associated labs under that lab category to it like this:

Lab	Lab Category	NA - Lab Count	Data Consumption	Priority Upgrade
1. Body and Chassis	Body and Chassis
a. Full Vehicle Test Sys	Body and Chassis	1	1	.65
b. Component Sys	Body and Chassis	1	1	.20
c. Some Other Sys	Body and Chassis	1	1	.4
d. Another System	Body and Chassis	1	1	.1
2. Electrical	Electrical
2. Electrical LAb	Electrical	1	1	.15
...etc.

Once my file is set up like this, I can pull off the prefixing and clean up the lab category heading rows that add no value to my data set. Thanks

[mbarone · Accepted solution]

In your sample, what I'll call your "primary category" is prefixed with a number, a period, then a single space.  And your sub-categories are not.  If that's exactly the case, then it can be done fairly efficiently with a Mutli-Row Formula tool.  Basically, there has to be something in the incoming data that can be identified which will separate out the primary category from the subs.

If indeed, the format of the primary category will be #, ., then space, you can use this:

[cblades3]

Thanks! I had a couple issues with it because the lab categories are more than one word in length but it worked out. I swapped \s with \d and then the new column had the lab categories with ' . ' in front but that was easy to trim off with a separate function 

[mbarone]

Glad to hear it. I figured it might need some tweaks but knew it would be enough to get you started.