A question with two parts .. please check attachment sir

[Kichan]

I got a question that
A-1,2,B-3,6 and the answer should be
A-1,A-2,B-3,B-6
The requirment is "A-" should be constant in first part and "B-" is constant in second part .. Can we do this kind of questions in Excel ie.. a question having a question with two parts

 

[jtakw]

Hi,

Try this:

Book3.xlsx
	A	B
1	1-2,3,6-3,4	1-2,1-3,6-3,6-4
2	A-7,5,D-3,4	A-7,A-5,D-3,D-4
3	G-d,c,H-v,b	G-d,G-c,H-v,H-b
4	A-1,2,B-3,6	A-1,A-2,B-3,B-6
Sheet17

Cell Formulas
Range	Formula
B1:B4	B1	=SUBSTITUTE(SUBSTITUTE(A1,",",","&LEFT(A1,2),1),",",","&MID(A1,7,2),3)

 

[Kichan]

Thank u for your replay sir..
Can u please check the attachment sir...your formula is only working with two digits after "A-" there will be more items after that "-" .. how can we do it in that kind of situation .. showed in the attachment picture

 

[jtakw]

Thank u for your replay sir..
Can u please check the attachment sir...your formula is only working with two digits after "A-" there will be more items after that "-" .. how can we do it in that kind of situation .. showed in the attachment picture

That's because your data is Different than what you posted in your OP sample, as in some of your other question threads. It's Always best to post Actual samples representative of your data, so helpers don't need to waste time modifying formulas as you trickle in the information.

Also, please post sample data using XL2BB (see my signature), or at least in Table format, so helpers don't need to manually type your data for testing, TIA.

I'll look into it later.

 

[Kichan]

Ok sir .. I wil send u sample data through XL2BB .. thank u

 

[Kichan]

Book1.xlsx
	A	B
1	1-1,2,3,5-8,9,10	1-1,1-2,1-3,5-8,5-9,5-10
2	B-5,6,7,12-1,2,3,100-1,2,3	B-5,B-6,B-7,12-1,12-2,12-3,100-1,100-2,100-3
3	15-1,2,3,8,4,5,6	15-1,15-2,15-3,15-8,15-4,15-5,15-6
4	1854-1,2,3,1525-1,2,3,4	1854-1,1854-2,1854-3,1525-1,1525-2,1525-3,1525-4
Sheet1

 

[Kichan]

B-5,6,7,12-1,2,3,100-1,2,3

B-5,B-6,B-7,12-1,12-2,12-3,100-1,100-2,100-3

in this case "B-5" is constant for 5,6,7 and "12-" is constant for 1,2,3 and "100-" is constant for 1,2,3
please see the attached thread attached below

Book1.xlsx
	A	B
1	1-1,2,3,5-8,9,10	1-1,1-2,1-3,5-8,5-9,5-10
2	B-5,6,7,12-1,2,3,100-1,2,3	B-5,B-6,B-7,12-1,12-2,12-3,100-1,100-2,100-3
3	15-1,2,3,8,4,5,6	15-1,15-2,15-3,15-8,15-4,15-5,15-6
4	1854-1,2,3,1525-1,2,3,4	1854-1,1854-2,1854-3,1525-1,1525-2,1525-3,1525-4
Sheet1

 

[jtakw]

I was trying to supply a single formula to solve your query, but after literally hours of trying, and due to the total inconsistency of your source text strings, I realized I was going down a rabbit hole.
So, I decided helper columns are needed, and more hours of trying, even with that, it's messy, but this works, Based only on your most recent data, hopefully it'll work for you.

You can Hide the "Helper Columns" if you'd like:

Book3.xlsx
	A	B	C	D	E	F
1		Hide helper 1	Hide helper 2	Hide helper 3	Hide helper 4	Results
2	1-2,3,6-3,4	1-2,3	6-3,4	6-3,4		1-2,1-3,6-3,6-4
3	A-7,5,D-3,4	A-7,5	D-3,4	D-3,4		A-7,A-5,D-3,D-4
4	G-d,c,H-v,b	G-d,c	H-v,b	H-v,b		G-d,G-c,H-v,H-b
5	A-1,2,B-3,6	A-1,2	B-3,6	B-3,6		A-1,A-2,B-3,B-6
6	3-1,2,4-6,7	3-1,2	4-6,7	4-6,7		3-1,3-2,4-6,4-7
7	3-1,2,3,4,6-3,4,5,6	3-1,2,3,4	6-3,4,5,6	6-3,4,5,6		3-1,3-2,3-3,3-4,6-3,6-4,6-5,6-6
8	D-4,6,3,H-5,8,5	D-4,6,3	H-5,8,5	H-5,8,5		D-4,D-6,D-3,H-5,H-8,H-5
9	U-5,8,0,4	U-5,8,0,4	U-5,8,0,4			U-5,U-8,U-0,U-4
10	1-1,2,3,5-8,9,10	1-1,2,3	5-8,9,10	5-8,9,10		1-1,1-2,1-3,5-8,5-9,5-10
11	B-5,6,7,12-1,2,3,100-1,2,3	B-5,6,7	12-1,2,3,100-1,2,3	12-1,2,3	100-1,2,3	B-5,B-6,B-7,12-1,12-2,12-3,100-1,100-2,100-3
12	15-1,2,3,8,4,5,6	15-1,2,3,8,4,5,6	15-1,2,3,8,4,5,6			15-1,15-2,15-3,15-8,15-4,15-5,15-6
13	1854-1,2,3,1525-1,2,3,4	1854-1,2,3	1525-1,2,3,4	1525-1,2,3,4		1854-1,1854-2,1854-3,1525-1,1525-2,1525-3,1525-4
Sheet17

Cell Formulas
Range	Formula
B2:B13	B2	=TRIM(LEFT(SUBSTITUTE(A2,",",IF(ISNUMBER(FIND("-",A2,FIND("-",A2)+1)),REPT(" ",100),","),LEN(LEFT(A2,FIND("-",A2&"-",FIND("-",A2)+1)))-LEN(SUBSTITUTE(LEFT(A2,FIND("-",A2&"-",FIND("-",A2)+1)),",",""))),100))
C2:C13	C2	=SUBSTITUTE(A2,TRIM(LEFT(SUBSTITUTE(A2,",",IF(ISNUMBER(FIND("-",A2,FIND("-",A2)+1)),REPT(" ",100),","),LEN(LEFT(A2,FIND("-",A2&"-",FIND("-",A2)+1)))-LEN(SUBSTITUTE(LEFT(A2,FIND("-",A2&"-",FIND("-",A2)+1)),",",""))),100))&",","")
D2:D13	D2	=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(C2,",",IF(ISNUMBER(FIND("-",C2,FIND("-",C2)+1)),REPT(" ",100),","),LEN(LEFT(C2,FIND("-",C2&"-",FIND("-",C2)+1)))-LEN(SUBSTITUTE(LEFT(C2,FIND("-",C2&"-",FIND("-",C2)+1)),",",""))),100)),B2,"")
E2:E13	E2	=IF(D2="","",MID(SUBSTITUTE(SUBSTITUTE(A2,B2&",",""),D2,""),2,99))
F2:F13	F2	=SUBSTITUTE(TRIM(SUBSTITUTE(B2,",",","&LEFT(B2,FIND("-",B2)))&" "&IFERROR(SUBSTITUTE(D2,",",","&LEFT(D2,FIND("-",D2))),"")&IFERROR(" "&SUBSTITUTE(E2,",",","&LEFT(E2,FIND("-",E2))),""))," ",",")

 

[Kichan]

You are a life saver sir .. this as literally for my girlfriend ..
Thank you very much ... Have a nice day..

 

[jtakw]

You're welcome, don't forget to mark solution post.