Parsing data from a cell

[HomePro]

I have one cell that can contain several data points i want to parse into dedicated columns. So 1 for wdo one for 4 pt one for wind mit, etc.
Can anyone help?

WDO Inspection - FL ONLY (by Primo Pest) ($149.00) | 4 Point Insurance Evaluation ($75.00) | Wind Mitigation Assessment ($75.00) | Single-Family Home (Residential Inspection) ($699.00) | Additional Price for properties 60 to 70 mile away ($10.00) | Convenience Fee ($35.28)

Water Testing ($249.00) | Single-Family Home (Residential Inspection) ($459.00) | WDO Inspection - FL ONLY (by Primo Pest) ($149.00) | 4 Point Insurance Evaluation ($75.00) | Wind Mitigation Assessment ($75.00) | Additional Price for properties 50 to 60 mile away ($10.00)

WDO Inspection (by Primo Pest) ($149.00) | 4 Point Insurance Evaluation ($75.00) | Wind Mitigation Assessment ($75.00) | Single-Family Home (Residential Inspection - FL) ($819.00) | Water Testing ($249.00) | Asbestos Testing - Bulk Materials ($99.00)

WDO Inspection - FL ONLY (by Primo Pest) ($149.00) | Single-Family Home (Residential Inspection) ($459.00) | Water Testing ($249.00) | Additional Price for properties 90 to 100 mile away ($40.00) | Convenience Fee ($31.40)

Single-Family Home (Residential Inspection) ($519.00) | 4 Point Insurance Evaluation ($75.00) | Wind Mitigation Assessment ($75.00) | Additional Price for properties 50 to 60 mile away ($10.00) | Convenience Fee ($23.77)

 

[DanteAmor]

Split text into different columns with the Convert Text to Columns Wizard.
Delimiter: "|"

Split text into different columns with the Convert Text to Columns Wizard - Microsoft Support

Learn how to take the text in one or more cells, and split it out across multiple cells by using Excel functions. This is called parsing, and is the opposite of concatenate. Watch more in this video.

support.microsoft.com

 

[HomePro]

WoW. that seems to easy. Ill try that. Tks

 

[HomePro]

So that kind of works. It does parse out the data into separate columns. Half way there, thanks.
So now i have 6 columns of data, however i need to move all the similar results into one column
1 column per data type. And now there spread across six columns randomly. How do you suggest I do that?

 

[DanteAmor]

I do not understand what do you need. Could you give an example of what you have and what you want as a result.
Use XL2BB tool minisheet for examples.

 

[HomePro]

So the data I am starting with is what I posted above. And the text to column pulls the dat out but as the data is not always in the same order the results span several columns. As per example the WDO over 255 entries populates into all 5 columns. I would like all the WDO in one column so I can sort by that. So if I wanted to know how many wdo I had I need to right now sort four columns 1 at a time and add up the totals manually. I would like them all in one column so I can filter it.

 

[DanteAmor]

Try the following formula, put the keyword or keywords in the header to extract each concept:

Dante Amor
	A	B	C	D	E	F	G
1		WDO	4 Point Insurance Evaluation	Wind Mitigation Assessment	Single-Family Home	Additional Price for properties	Convenience Fee
2	WDO Inspection - FL ONLY (by Primo Pest) ($149.00) | 4 Point Insurance Evaluation ($75.00) | Wind Mitigation Assessment ($75.00) | Single-Family Home (Residential Inspection) ($699.00) | Additional Price for properties 60 to 70 mile away ($10.00) | Convenience Fee ($35.28)	WDO Inspection - FL ONLY (by Primo Pest) ($149.00)	4 Point Insurance Evaluation ($75.00)	Wind Mitigation Assessment ($75.00)	Single-Family Home (Residential Inspection) ($699.00)	Additional Price for properties 60 to 70 mile away ($10.00)	Convenience Fee ($35.28)
3	Water Testing ($249.00) | Single-Family Home (Residential Inspection) ($459.00) | WDO Inspection - FL ONLY (by Primo Pest) ($149.00) | 4 Point Insurance Evaluation ($75.00) | Wind Mitigation Assessment ($75.00) | Additional Price for properties 50 to 60 mile away ($10.00)	WDO Inspection - FL ONLY (by Primo Pest) ($149.00)	4 Point Insurance Evaluation ($75.00)	Wind Mitigation Assessment ($75.00)	Single-Family Home (Residential Inspection) ($459.00)	Additional Price for properties 50 to 60 mile away ($10.00)
4	WDO Inspection (by Primo Pest) ($149.00) | 4 Point Insurance Evaluation ($75.00) | Wind Mitigation Assessment ($75.00) | Single-Family Home (Residential Inspection - FL) ($819.00) | Water Testing ($249.00) | Asbestos Testing - Bulk Materials ($99.00)	WDO Inspection (by Primo Pest) ($149.00)	4 Point Insurance Evaluation ($75.00)	Wind Mitigation Assessment ($75.00)	Single-Family Home (Residential Inspection - FL) ($819.00)
5	WDO Inspection - FL ONLY (by Primo Pest) ($149.00) | Single-Family Home (Residential Inspection) ($459.00) | Water Testing ($249.00) | Additional Price for properties 90 to 100 mile away ($40.00) | Convenience Fee ($31.40)	WDO Inspection - FL ONLY (by Primo Pest) ($149.00)			Single-Family Home (Residential Inspection) ($459.00)	Additional Price for properties 90 to 100 mile away ($40.00)	Convenience Fee ($31.40)
6	Single-Family Home (Residential Inspection) ($519.00) | 4 Point Insurance Evaluation ($75.00) | Wind Mitigation Assessment ($75.00) | Additional Price for properties 50 to 60 mile away ($10.00) | Convenience Fee ($23.77)		4 Point Insurance Evaluation ($75.00)	Wind Mitigation Assessment ($75.00)	Single-Family Home (Residential Inspection) ($519.00)	Additional Price for properties 50 to 60 mile away ($10.00)	Convenience Fee ($23.77)
Hoja5

Cell Formulas
Range	Formula
B2:G6	B2	=IFERROR(TRIM(LEFT(SUBSTITUTE(REPLACE($A2,1,SEARCH(B$1,$A2)-1,""),"|",REPT(" ",999)),999)),"")

 

[HomePro]

Dante,
I will try that later today when I get time but this looks AWESOME.... Tks

 

[HomePro]

Works Like A Charm. Thank you

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.