DISCONCATENATE … UNCONCATENATE… Lose the CONCATENATION…!!!

[How_Do_I]

What a title... LOL

Sorry, I should know this but I can’t think how to do it…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
How do I “split” my return in A1 to get the name before the "|" in C1 and the name after the "|" in D1 please?

Excel Workbook
	A	B	C	D
1	Norwich City|Watford		Norwich City	Watford
Sheet1

<o></o>

 

[shift-del]

Hello

C1: =MID(A1,1,SEARCH("|",A1)-1)
D1: =MID(A1,SEARCH("|",A1)+1,9^9)

Or text-to-columns, but then the results would occur in A1 and B1.

 

[How_Do_I]

Hello

C1: =MID(A1,1,SEARCH("|",A1)-1)
D1: =MID(A1,SEARCH("|",A1)+1,9^9)

Or text-to-columns, but then the results would occur in A1 and B1.

Thanks for that, it works!... Could I just ask about the 9^9 though… Is that a way of dealing with an x amount of characters’?

Excel Workbook
	A	B	C	D
1	Norwich City|Watford		Norwich City	Watford
2	Bristol City|Millwall		Bristol City	Millwall
3	Coventry City|Portsmouth		Coventry City	Portsmouth
4	Hull City|Swansea City		Hull City	Swansea City
5	Preston North End|Doncaster Rovers		Preston North End	Doncaster Rovers
6	QPR|Barnsley		QPR	Barnsley
7	Reading|Scunthorpe United		Reading	Scunthorpe United
Sheet1

 

[Peter_SSs]

Or text-to-columns, but then the results would occur in A1 and B1.

I would use Text to Columns. At step 3 of the wizard you could enter C1 in the 'Destination:' box.

Edit: If using formulas, I would use
C1: =LEFT(A1,FIND("|",A1)-1)
D1: =SUBSTITUTE(A1,C1&"|","")

 

[shift-del]

Could I just ask about the 9^9 though… Is that a way of dealing with an x amount of characters’?

You could calculate the exact length, but since every number greater than that also works, I prefer 9^9 or 999.

 

[How_Do_I]

Thanks Peter, thanks shift-del, this one is sorted now...!