RE-ARRANGE TEXT STRING ORDER DUE TO A WORD "TO"

[AFZAL SOHAIL]

Hello!,Dear Team, I need a formula who re-arrange the Text String in a Cell with reference to the word in this text string is "to"
My Data is as under:-

8078-AIT (2/0/1) to C-12 Neelam Block

Allama Iqbal town 8078 (2/0/1) to C-12 Neelam Block

8061 ARZ (11/0/2) to Dream Garden MSAG

8061 ARZ (2/0/6) to MSAG-56 Jalal Estate Edenabad

8061-ARZ to 8060-BTN

AliRazabad OLT to C04 Hajvery Textile

8061-ARZ (11/0/7) to C-3/4 Bhobhtian Chowk

ARZ OLT-1 to C-14 M-2 Lake City

BTN: OLT-1 to Leads Club Defence Road

JTN OLT-1 to Bahria Orchard (Indoor)

BTN OLT-1 to Near Lead Club

BTN OLT-1 to Near Lead Club

Faisal Town 8067 to Ufone 7123

FZRD: OLT-1 to C-10 Main Chungi Amrsadhu

8057-FZRD (11/0/3) to 7301-Ufone (2/0/2)

And the result should be is as under, thanks a lot for your help:-

C-12 Neelam Block to 8078-AIT (2/0/1)

C-12 Neelam Block to Allama Iqbal town 8078 (2/0/1)

Dream Garden MSAG to 8061 ARZ (11/0/2)

MSAG-56 Jalal Estate Edenabad to 8061 ARZ (2/0/6)

8060-BTN to 8061-ARZ

C04 Hajvery Textile to AliRazabad OLT

C-3/4 Bhobhtian Chowk to 8061-ARZ (11/0/7)

C-14 M-2 Lake City to ARZ OLT-1

Leads Club Defence Road to BTN: OLT-1

Bahria Orchard (Indoor) to JTN OLT-1

Near Lead Club to BTN OLT-1

Near Lead Club to BTN OLT-1

Ufone 7123 to Faisal Town 8067

C-10 Main Chungi Amrsadhu to FZRD: OLT-1

7301-Ufone (2/0/2) to 8057-FZRD (11/0/3)

 

[Sufiyan97]

Try

Book2
	A	B	C
1	8078-AIT (2/0/1) to C-12 Neelam Block	C-12 Neelam Block to 8078-AIT (2/0/1)
2	Allama Iqbal town 8078 (2/0/1) to C-12 Neelam Block	C-12 Neelam Block to Allama Iqbal town 8078 (2/0/1)
3	8061 ARZ (11/0/2) to Dream Garden MSAG	Dream Garden MSAG to 8061 ARZ (11/0/2)
4	8061 ARZ (2/0/6) to MSAG-56 Jalal Estate Edenabad	MSAG-56 Jalal Estate Edenabad to 8061 ARZ (2/0/6)
5	8061-ARZ to 8060-BTN	8060-BTN to 8061-ARZ
6	AliRazabad OLT to C04 Hajvery Textile	C04 Hajvery Textile to AliRazabad OLT
7	8061-ARZ (11/0/7) to C-3/4 Bhobhtian Chowk	C-3/4 Bhobhtian Chowk to 8061-ARZ (11/0/7)
8	ARZ OLT-1 to C-14 M-2 Lake City	C-14 M-2 Lake City to ARZ OLT-1
9	BTN: OLT-1 to Leads Club Defence Road	Leads Club Defence Road to BTN: OLT-1
10	JTN OLT-1 to Bahria Orchard (Indoor)	Bahria Orchard (Indoor) to JTN OLT-1
11	BTN OLT-1 to Near Lead Club	Near Lead Club to BTN OLT-1
12	BTN OLT-1 to Near Lead Club	Near Lead Club to BTN OLT-1
13	Faisal Town 8067 to Ufone 7123	Ufone 7123 to Faisal Town 8067
14	FZRD: OLT-1 to C-10 Main Chungi Amrsadhu	C-10 Main Chungi Amrsadhu to FZRD: OLT-1
15	8057-FZRD (11/0/3) to 7301-Ufone (2/0/2)	7301-Ufone (2/0/2) to 8057-FZRD (11/0/3)
16
RE-ARRANGE TEXT

Cell Formulas
Range	Formula
B1:B15	B1	=TRIM(REPLACE(A1,1,SEARCH(" to ",A1)+3,"")&" to "&LEFT(A1,SEARCH(" to ",A1)))

 

[Fluff]

For xl 2021 how about

Fluff.xlsm
	A	B
1
2	8078-AIT (2/0/1) to C-12 Neelam Block	C-12 Neelam Block to 8078-AIT (2/0/1)
3	Allama Iqbal town 8078 (2/0/1) to C-12 Neelam Block	C-12 Neelam Block to Allama Iqbal town 8078 (2/0/1)
4	8061 ARZ (11/0/2) to Dream Garden MSAG	Dream Garden MSAG to 8061 ARZ (11/0/2)
5	8061 ARZ (2/0/6) to MSAG-56 Jalal Estate Edenabad	MSAG-56 Jalal Estate Edenabad to 8061 ARZ (2/0/6)
6	8061-ARZ to 8060-BTN	8060-BTN to 8061-ARZ
7	AliRazabad OLT to C04 Hajvery Textile	C04 Hajvery Textile to AliRazabad OLT
8	8061-ARZ (11/0/7) to C-3/4 Bhobhtian Chowk	C-3/4 Bhobhtian Chowk to 8061-ARZ (11/0/7)
9	ARZ OLT-1 to C-14 M-2 Lake City	C-14 M-2 Lake City to ARZ OLT-1
10	BTN: OLT-1 to Leads Club Defence Road	Leads Club Defence Road to BTN: OLT-1
11	JTN OLT-1 to Bahria Orchard (Indoor)	Bahria Orchard (Indoor) to JTN OLT-1
12	BTN OLT-1 to Near Lead Club	Near Lead Club to BTN OLT-1
13	BTN OLT-1 to Near Lead Club	Near Lead Club to BTN OLT-1
14	Faisal Town 8067 to Ufone 7123	Ufone 7123 to Faisal Town 8067
15	FZRD: OLT-1 to C-10 Main Chungi Amrsadhu	C-10 Main Chungi Amrsadhu to FZRD: OLT-1
16	8057-FZRD (11/0/3) to 7301-Ufone (2/0/2)	7301-Ufone (2/0/2) to 8057-FZRD (11/0/3)
Master

Cell Formulas
Range	Formula
B2:B16	B2	=LET(f,FILTERXML("<k><m>"&SUBSTITUTE(A2," to ","</m><m>")&"</m></k>","//m"),INDEX(f,2)&" to "& INDEX(f,1))

 

[AFZAL SOHAIL]

Hello Team,
Thanks a lot both two formulas working very well and very fast.
Again thanks to this Forum

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

What about this much simpler one?

23 06 06.xlsm
	A	B
1
2	8078-AIT (2/0/1) to C-12 Neelam Block	C-12 Neelam Block to 8078-AIT (2/0/1)
3	Allama Iqbal town 8078 (2/0/1) to C-12 Neelam Block	C-12 Neelam Block to Allama Iqbal town 8078 (2/0/1)
4	8061 ARZ (11/0/2) to Dream Garden MSAG	Dream Garden MSAG to 8061 ARZ (11/0/2)
5	8061 ARZ (2/0/6) to MSAG-56 Jalal Estate Edenabad	MSAG-56 Jalal Estate Edenabad to 8061 ARZ (2/0/6)
6	8061-ARZ to 8060-BTN	8060-BTN to 8061-ARZ
7	AliRazabad OLT to C04 Hajvery Textile	C04 Hajvery Textile to AliRazabad OLT
8	8061-ARZ (11/0/7) to C-3/4 Bhobhtian Chowk	C-3/4 Bhobhtian Chowk to 8061-ARZ (11/0/7)
9	ARZ OLT-1 to C-14 M-2 Lake City	C-14 M-2 Lake City to ARZ OLT-1
10	BTN: OLT-1 to Leads Club Defence Road	Leads Club Defence Road to BTN: OLT-1
11	JTN OLT-1 to Bahria Orchard (Indoor)	Bahria Orchard (Indoor) to JTN OLT-1
12	BTN OLT-1 to Near Lead Club	Near Lead Club to BTN OLT-1
13	BTN OLT-1 to Near Lead Club	Near Lead Club to BTN OLT-1
14	Faisal Town 8067 to Ufone 7123	Ufone 7123 to Faisal Town 8067
15	FZRD: OLT-1 to C-10 Main Chungi Amrsadhu	C-10 Main Chungi Amrsadhu to FZRD: OLT-1
16	8057-FZRD (11/0/3) to 7301-Ufone (2/0/2)	7301-Ufone (2/0/2) to 8057-FZRD (11/0/3)
Swap to

Cell Formulas
Range	Formula
B2:B16	B2	=MID(A2&" to "&A2,FIND(" to ",A2)+4,LEN(A2))

 

[AFZAL SOHAIL]

Thanks a lot Sir,
This is very nice and simple formula,
If I want to match both two coloumns what will be the formula, again thanks

 

[Peter_SSs]

Thanks a lot Sir,
This is very nice and simple formula,

You're welcome. Thanks for the follow-up.

If I want to match both two coloumns what will be the formula, again thanks

If you are still looking for an answer to this please clarify with further explanation and examples as I don't understand what you are wanting.

 

[AFZAL SOHAIL]

Thanks a lot Sir Peter_SSs,
I want to match col-A with col-b and then find any duplicate with formula not conditional formatting.

078-AIT (2/0/1) to C-12 Neelam Block

C-12 Neelam Block to 8078-AIT (2/0/1)

Allama Iqbal town 8078 (2/0/1) to C-12 Neelam Block

C-12 Neelam Block to Allama Iqbal town 8078 (2/0/1)

8061 ARZ (11/0/2) to Dream Garden MSAG

Dream Garden MSAG to 8061 ARZ (11/0/2)

8061 ARZ (2/0/6) to MSAG-56 Jalal Estate Edenabad

MSAG-56 Jalal Estate Edenabad to 8061 ARZ (2/0/6)

8061-ARZ to 8060-BTN

8060-BTN to 8061-ARZ

AliRazabad OLT to C04 Hajvery Textile

C04 Hajvery Textile to AliRazabad OLT

8061-ARZ (11/0/7) to C-3/4 Bhobhtian Chowk

C-3/4 Bhobhtian Chowk to 8061-ARZ (11/0/7)

ARZ OLT-1 to C-14 M-2 Lake City

C-14 M-2 Lake City to ARZ OLT-1

BTN: OLT-1 to Leads Club Defence Road

Leads Club Defence Road to BTN: OLT-1

JTN OLT-1 to Bahria Orchard (Indoor)

Bahria Orchard (Indoor) to JTN OLT-1

 

[Sufiyan97]

With Peter's formula

Do you want something like?

Book4
	A	B	C	D
1	078-AIT (2/0/1) to C-12 Neelam Block	C-12 Neelam Block to 8078-AIT (2/0/1)	#N/A
2	Allama Iqbal town 8078 (2/0/1) to C-12 Neelam Block	C-12 Neelam Block to Allama Iqbal town 8078 (2/0/1)	2
3	8061 ARZ (11/0/2) to Dream Garden MSAG	Dream Garden MSAG to 8061 ARZ (11/0/2)	3
4	8061 ARZ (2/0/6) to MSAG-56 Jalal Estate Edenabad	MSAG-56 Jalal Estate Edenabad to 8061 ARZ (2/0/6)	4
5	8061-ARZ to 8060-BTN	8060-BTN to 8061-ARZ	5
6	AliRazabad OLT to C04 Hajvery Textile	C04 Hajvery Textile to AliRazabad OLT	6
7	8061-ARZ (11/0/7) to C-3/4 Bhobhtian Chowk	C-3/4 Bhobhtian Chowk to 8061-ARZ (11/0/7)	7
8	ARZ OLT-1 to C-14 M-2 Lake City	C-14 M-2 Lake City to ARZ OLT-1	8
9	BTN: OLT-1 to Leads Club Defence Road	Leads Club Defence Road to BTN: OLT-1	9
10	JTN OLT-1 to Bahria Orchard (Indoor)	Bahria Orchard (Indoor) to JTN OLT-1	10
11
12
Sheet1

Cell Formulas
Range	Formula
C1:C10	C1	=MATCH(MID(B1&" to "&B1,FIND(" to ",B1)+4,LEN(B1)),$A$1:$A$10,0)