Help me with an excel formula to convert a string to another

[mechrobin]

Hi All,

I want to convert following string

P4-128-HS-29039-128299-3-SP01​

to

4300-SP-XH-DL-128299-128-HS-29039-03-SP01-Q​

Please note length of the first string may vary for example

U4-126-ETT-11024-126101-10-SP01​

 

[mechrobin]

ORGINAL STRING​	CHANGE TO​
P4-128-HS-29039-128299-3-SP01​	4300-SP-XH-DL-128299-128-HS-29039-03-SP01-Q​
U4-126-ETT-11024-126101-10-SP01​	4300-SP-XH-DL-126101-126-ETT-11024-10-SP01-Q​

 

[FormR]

Hi, perhaps this will work for you.

Book1
	A	B
1	ORGINAL STRING	CHANGE TO
2	P4-128-HS-29039-128299-3-SP01	4300-SP-XH-DL-128299-128-HS-29039-03-SP01-Q
3	U4-126-ETT-11024-126101-10-SP01	4300-SP-XH-DL-126101-126-ETT-11024-10-SP01-Q
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTJOIN("-",0,"4300-SP-XH-DL",CHOOSECOLS(TEXT(TEXTSPLIT(A2,"-"),"00"),{5,2,3,4,6,7}),"Q")

 

[mechrobin]

Hi, perhaps this will work for you.

Book1
	A	B
1	ORGINAL STRING	CHANGE TO
2	P4-128-HS-29039-128299-3-SP01	4300-SP-XH-DL-128299-128-HS-29039-03-SP01-Q
3	U4-126-ETT-11024-126101-10-SP01	4300-SP-XH-DL-126101-126-ETT-11024-10-SP01-Q
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTJOIN("-",0,"4300-SP-XH-DL",CHOOSECOLS(TEXT(TEXTSPLIT(A2,"-"),"00"),{5,2,3,4,6,7}),"Q")

Hi @FormR thanks for your help. But it's not working for some text. For eg.

P4-131-DI-00001-131002-4-SP03

4300-SP-XH-DL-131002-131-DI-01-04-SP03-Q.pdf

In this result should be

4300-SP-XH-DL-131002-131-DI-00001-04-SP03-Q.pdf

 

[FormR]

But it's not working for some text. For eg.

Hi, it doesn't help only having a couple of examples and not having any explanation of the translation. But you can give this a try:

Book2
	A	B
1	ORGINAL STRING	CHANGE TO
2	P4-128-HS-29039-128299-3-SP01	4300-SP-XH-DL-128299-128-HS-29039-03-SP01-Q.pdf
3	U4-126-ETT-11024-126101-10-SP01	4300-SP-XH-DL-126101-126-ETT-11024-10-SP01-Q.pdf
4	P4-131-DI-00001-131002-4-SP03	4300-SP-XH-DL-131002-131-DI-00001-04-SP03-Q.pdf
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LET(CC,TEXTSPLIT(A2,"-"),TEXTJOIN("-",0,"4300-SP-XH-DL",INDEX(CC,0,{5,2,3,4}),TEXT(INDEX(CC,0,6),"00"),INDEX(CC,0,7),"Q.pdf"))

 

[mechrobin]

Hi, it doesn't help only having a couple of examples and not having any explanation of the translation. But you can give this a try:

Book2
	A	B
1	ORGINAL STRING	CHANGE TO
2	P4-128-HS-29039-128299-3-SP01	4300-SP-XH-DL-128299-128-HS-29039-03-SP01-Q.pdf
3	U4-126-ETT-11024-126101-10-SP01	4300-SP-XH-DL-126101-126-ETT-11024-10-SP01-Q.pdf
4	P4-131-DI-00001-131002-4-SP03	4300-SP-XH-DL-131002-131-DI-00001-04-SP03-Q.pdf
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LET(CC,TEXTSPLIT(A2,"-"),TEXTJOIN("-",0,"4300-SP-XH-DL",INDEX(CC,0,{5,2,3,4}),TEXT(INDEX(CC,0,6),"00"),INDEX(CC,0,7),"Q.pdf"))

Thank you so much. It's worked