Separation Formula Required

[ShoaibAli]

I want to separate name and id from ColA to ColB as name and ColC as ID's please share formula.

Name / Primary - ID / Secondary - ID	Name	Primary - ID / Secondary - ID
Kashif Khan - 526584944	Kashif Khan	526584944
Shoaib Ali - 25624621 / Jahanzain Rajput - 265565414	Shoaib Ali / Jahanzain Rajput	25624621 / 265565414
PVT - 111111111 / Kashif Khan - 526584944	PVT / Kashif Khan	111111111 / 526584944

 

[ShoaibAli]

amiroo

Thank you So much its working.

 

[RoryA]

Peter's formula requires Office 365 on a Monthly update channel. Since your profile says you have every current Windows version of Excel, it was a reasonable suggestion.

 

[Peter_SSs]

Edit: Ah, I see both of my comments have already been addressed.

It not working look.

It was written for Excel 365 which your profile says you have.

If this must work in other versions, how many name/id combinations could there be in a cell? If only 2, another suggestion has already been made for that. have you tried that?

 

[ShoaibAli]

Peter_SSs
Thanks Peter I will try it on 365.

 

[ShoaibAli]

RoryA


Please help if i separate the all required field as below then which formulas will be used i have tried but could not succeeed.

 

[Peter_SSs]

if i separate the all required field as below then which formulas will be used

Try these

ShoaibAli 2020-02-29 1.xlsm
	A	B	C	D	E
1	Name / Primary - ID / Secondary - ID	Name	ID	Name	ID
2	Kashif Khan - 526584944	Kashif Khan	526584944
3	Shoaib Ali - 25624621 / Jahanzain Rajput - 265565414	Shoaib Ali	25624621	Jahanzain Rajput	265565414
4	PVT - 111111111 / Kashif Khan - 526584944	PVT	111111111	Kashif Khan	526584944
Sheet1 (2)

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(LEFT(A2,FIND("-",A2)-1))
C2:C4	C2	=TRIM(SUBSTITUTE(LEFT(A2,FIND("/",A2&"/")-1),B2&" -",""))
D2:D4	D2	=IF(FIND("/",A2&"/")<LEN(A2),TRIM(REPLACE(LEFT(A2,FIND("-",A2,FIND("/",A2))-1),1,FIND("/",A2),"")),"")
E2:E4	E2	=IF(D2="","",TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",20)),20)))

 

[ShoaibAli]

Peter_SSs

Please look at the errors which are highlighted

Primary - ID / Secondary - ID
UNITED HEALTHCARE - 922837002	UNITED HEALTHCARE	922837002
HEALTH FIRST - 133615803 / MEDICARE- NATL - 112382762a	HEALTH FIRST	133615803	MEDICARE	112382762a
HEALTHFIRST MEDICAID - QE97026X	HEALTHFIRST MEDICAID	QE97026X
MEDICARE- NATL - 122408242A	MEDICARE	MEDICARE- NATL - 122408242A
ELDERPLAN - 01319115801 / MEDICARE- NATL - 114660345A	ELDERPLAN	01319115801	MEDICARE	114660345A
METROPLUS ESSENTIAL PLAN - 630382391	METROPLUS ESSENTIAL PLAN	630382391
MEDICARE- NATL - 9KM8AW3NK46 / MEDICAID - PK22234B	MEDICARE	MEDICARE- NATL - 9KM8AW3NK46	MEDICAID	PK22234B
MEDICAID - YW71071D	MEDICAID	YW71071D
FIDELES MEDICAID - 74280680900	FIDELES MEDICAID	74280680900
AETNA - MEBP07GF / MEDICARE- NATL - 110741820A	AETNA	MEBP07GF	MEDICARE	110741820A
PVT - 111111111	PVT	111111111
TRICARE - 00081456800 / 1199 SEIU - 9800569926	TRICARE	00081456800	1199 SEIU	9800569926
MEDICARE- NATL - 058467921A	MEDICARE	MEDICARE- NATL - 058467921A
EMBLEM HEALTH - 932539946	EMBLEM HEALTH	932539946
HEALTHFIRST MEDICAID - PW15673P	HEALTHFIRST MEDICAID	PW15673P
FIDELES MEDICAID - 74482085900 / MEDICAID - UW78283N	FIDELES MEDICAID	74482085900	MEDICAID	UW78283N
HEALTHFIRST MEDICAID - KZ36913K / MEDICAID - KZ36913K	HEALTHFIRST MEDICAID	KZ36913K	MEDICAID	KZ36913K
EMPIRE BCBS - DPE84885086	EMPIRE BCBS	DPE84885086
HEALTH FIRST - KX64024B	HEALTH FIRST	KX64024B
AFFINITY ESSENTIAL - 15050075500	AFFINITY ESSENTIAL	15050075500
HEALTHFIRST MEDICAID - XN22361J	HEALTHFIRST MEDICAID	XN22361J
HEALTHFIRST ESSENTIAL PLAN - 420000023075600 / MEDICAID - KR13482D	HEALTHFIRST ESSENTIAL PLAN	420000023075600	MEDICAID	KR13482D
EMBLEM HEALTH - 19114779	EMBLEM HEALTH	19114779
ELDERPLAN - 01374807101 / MEDICARE- NATL - 129742347A	ELDERPLAN	01374807101	MEDICARE	129742347A
UNITED COMMUNITY PLAN - 116594426	UNITED COMMUNITY PLAN	116594426

 

[Peter_SSs]

Please look at the errors which are highlighted

Oops, I missed seeing the Names that included the "-" character in your previous sample.

See if this is better.

ShoaibAli 2020-02-29 1.xlsm
	A	B	C	D	E
1	Name / Primary - ID / Secondary - ID	Name	ID	Name	ID
2	UNITED HEALTHCARE - 922837002	UNITED HEALTHCARE	922837002
3	HEALTH FIRST - 133615803 / MEDICARE- NATL - 112382762a	HEALTH FIRST	133615803	MEDICARE- NATL	112382762a
4	HEALTHFIRST MEDICAID - QE97026X	HEALTHFIRST MEDICAID	QE97026X
5	MEDICARE- NATL - 122408242A	MEDICARE- NATL	122408242A
6	ELDERPLAN - 01319115801 / MEDICARE- NATL - 114660345A	ELDERPLAN	01319115801	MEDICARE- NATL	114660345A
7	METROPLUS ESSENTIAL PLAN - 630382391	METROPLUS ESSENTIAL PLAN	630382391
8	MEDICARE- NATL - 9KM8AW3NK46 / MEDICAID - PK22234B	MEDICARE- NATL	9KM8AW3NK46	MEDICAID	PK22234B
9	MEDICAID - YW71071D	MEDICAID	YW71071D
10	FIDELES MEDICAID - 74280680900	FIDELES MEDICAID	74280680900
11	AETNA - MEBP07GF / MEDICARE- NATL - 110741820A	AETNA	MEBP07GF	MEDICARE- NATL	110741820A
12	PVT - 111111111	PVT	111111111
13	TRICARE - 00081456800 / 1199 SEIU - 9800569926	TRICARE	00081456800	1199 SEIU	9800569926
14	MEDICARE- NATL - 058467921A	MEDICARE- NATL	058467921A
15	EMBLEM HEALTH - 932539946	EMBLEM HEALTH	932539946
16	HEALTHFIRST MEDICAID - PW15673P	HEALTHFIRST MEDICAID	PW15673P
17	FIDELES MEDICAID - 74482085900 / MEDICAID - UW78283N	FIDELES MEDICAID	74482085900	MEDICAID	UW78283N
18	HEALTHFIRST MEDICAID - KZ36913K / MEDICAID - KZ36913K	HEALTHFIRST MEDICAID	KZ36913K	MEDICAID	KZ36913K
19	EMPIRE BCBS - DPE84885086	EMPIRE BCBS	DPE84885086
20	HEALTH FIRST - KX64024B	HEALTH FIRST	KX64024B
21	AFFINITY ESSENTIAL - 15050075500	AFFINITY ESSENTIAL	15050075500
22	HEALTHFIRST MEDICAID - XN22361J	HEALTHFIRST MEDICAID	XN22361J
23	HEALTHFIRST ESSENTIAL PLAN - 420000023075600 / MEDICAID - KR13482D	HEALTHFIRST ESSENTIAL PLAN	420000023075600	MEDICAID	KR13482D
24	EMBLEM HEALTH - 19114779	EMBLEM HEALTH	19114779
25	ELDERPLAN - 01374807101 / MEDICARE- NATL - 129742347A	ELDERPLAN	01374807101	MEDICARE- NATL	129742347A
26	UNITED COMMUNITY PLAN - 116594426	UNITED COMMUNITY PLAN	116594426
Sheet1 (4)

Cell Formulas
Range	Formula
B2:B26	B2	=TRIM(LEFT(A2,FIND(C2,A2)-3))
C2:C26	C2	=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,FIND("/",A2&"/")-1))," ",REPT(" ",30)),30))
D2:D26	D2	=IF(E2="","",TRIM(REPLACE(LEFT(A2,LEN(A2)-LEN(E2)-3),1,FIND("/",A2),"")))
E2:E26	E2	=IF(FIND("/",A2&"/")<LEN(A2),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",30)),30)),"")

 

[ShoaibAli]

Thank you for your consistent help.

 

[Peter_SSs]

Thank you for your consistent help.

No problem.