Help extracting strings from a cloud dataset

Hey Excel wizards,

I need a hand manipulating an exceptionally fun cloud dataset. The 'tags' have all been lumped into one column and I need to find a way to split out the below tag headers with their corresponding tag values.

I have attached a dummy dataset with the tag column as well as the 9 tag names we need to extract.

As an example:

The tag column follows the below format. The tag header is the alternating field with the suffix of : (e.g. applicationName is the tag header and Biztalk is the tag value)

"applicationName": "Biztalk","managedBy": "[email protected]","ownedBy": "[email protected]","projectName": "ztalk"

There is no order to which tag headers appear where. However, the structure of "header": followed by ","value"" is consistent.

In a nutshell, I need to find a way to search for the 9 tag headers listed in my spreadsheet and extract the respective tag values.

Hope this makes sense.

Thanks all

Do you still use Excel 2013 or something newer?

Please replace question marks in your sample set with the data you expect to see extracted.

Which 'latest' version? Please be specific and update your profile accordingly.

Assuming 365, in B2 copied across and down:

=LET(i,SUBSTITUTE(TEXTSPLIT($A2,":",","),"""",""),IFERROR(TRIM(INDEX(INDEX(i,,2),MATCH(TRUE,EXACT(B$1,INDEX(i,,1)),0))),""))

Shout if you want an explanation.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Yes, sure.

SUBSTITUTE(TEXTSPLIT($A2,":",","),"""","")

produces this:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	B	C
6	applicationName	Biz
7	managedBy	[email protected]
8	ownedBy	[email protected]
9	projectName	ztalk
10	businessUnit	ENT
11	requestID	M-PRJ0048360
12	environment	DEV
13	dataClassification	Confidential
14	costCentre	Z3064N
15	telstraBackup	POL01
16	telstraManaged	true
17	telstraPatching	Thu9PM_Win
18	costcentre	Z3001N

Sheet: Sheet1

and writes it to memory as a parameter (i).

IFERROR(TRIM(INDEX(INDEX(i,,2),MATCH(TRUE,EXACT(B$1,INDEX(i,,1)),0))),"")

uses the parameter (i) as a lookup table to find matches. The EXACT function within the INDEX MATCH makes the lookup case-sensitive.

You're welcome. Please mark this (and ALL of your previous threads) as SOLVED.

Just for fun... no copy/paste needed. All the results in one go.

Just delete all expected results and paste this into B2. If you need more rows, just change the bit in RED.

Formula:

Please Login or Register  to view this content.