Function to Break Apart Fields

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!

Workflow

Accepted answers

mbarone

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:

All comments

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

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335