SOLVED

Formula to separate text into by specific character

%3CLINGO-SUB%20id%3D%22lingo-sub-1664868%22%20slang%3D%22en-US%22%3EFormula%20to%20separate%20text%20into%20by%20specific%20character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1664868%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20String%20of%20Text%20that%20I%20would%20like%20to%20separate%20using%20a%20formula%20vs%20Txt%20-%26gt%3B%20Column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20Sample%3CBR%20%2F%3E03-08-2822%20-%20Marc'%20Pizza%20-%20Pittsburgh%2C%20PA%20-%20181%3C%2FP%3E%3CP%3EI%20want%20to%20separate%20at%20the%203rd%204th%20and%205th%20%22-%22%20dash%20VIA%20FORMULA%3C%2FP%3E%3CP%3Eso%20my%20sheet%20would%20look%20like%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3E03-08-2822%20-%20Marc'%20Pizza%20-%20Pittsburgh%2C%20PA%20-%20181%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E03-08-2822%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EMarc'%20Pizza%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EPittsburgh%2C%20PA%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E181%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1664868%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1665061%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20separate%20text%20into%20by%20specific%20character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1665061%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F793082%22%20target%3D%22_blank%22%3E%40UniversalAtlantic%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20assume%20the%20numbers%20in%20the%20beginning%20are%20the%20same%20length.%20If%20so%20please%20find%20the%20excel%20implementation.%3C%2FP%3E%3CP%3Eprovide%20me%20with%20feedback%20if%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1665116%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20separate%20text%20into%20by%20specific%20character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1665116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746148%22%20target%3D%22_blank%22%3E%40Ramiz_Assaf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20exactly%20what%20I'm%20looking%20for%20thank%20you%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

 

I have a String of Text that I would like to separate using a formula vs Txt -> Column.

 

Here is a Sample
03-08-2822 - Marc' Pizza - Pittsburgh, PA - 181

I want to separate at the 3rd 4th and 5th "-" dash VIA FORMULA

so my sheet would look like this

 

03-08-2822 - Marc' Pizza - Pittsburgh, PA - 18103-08-2822Marc' PizzaPittsburgh, PA181

 

Any help would be appreciated 

3 Replies
best response confirmed by UniversalAtlantic (Occasional Contributor)
Solution

@UniversalAtlantic 

 

I assume the numbers in the beginning are the same length. If so please find the excel implementation.

provide me with feedback if needed.

@Ramiz_Assaf 

 

This is exactly what I'm looking for thank you 

@UniversalAtlantic 

As variant

=TRANSPOSE(FILTERXML("<r><n>" & SUBSTITUTE(A4,"- ","</n><n>") & "</n></r>",  "//n"))