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

 

[amiroo]

Hi,

you can do it with flash fill tool. is that necessary to use formula?

 

[ShoaibAli]

Yes it is necessary to do with formula it can be done With Left, right and mid formula

Also posted here

Separation Formula Required - OzGrid Free Excel/VBA Help Forum

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... https://www.mrexcel.com/board/threads/separation-formula-required.1125877/

www.ozgrid.com

 

[amiroo]

OK, well you can do it with below formulas:

for name column:

=LEFT(A2,FIND("-",A2)-1)&IFERROR(MID(A2,FIND("/",A2),FIND("-",A2,FIND("/",A2))-FIND("/",A2)),"")

and for ID you can use this formula:

=IFERROR(MID(A2,FIND("-",A2)+1,FIND("/",A2)-FIND("-",A2)),RIGHT(A2,LEN(A2)-FIND("-",A2)))&IFERROR(RIGHT(A2,LEN(A2)-FIND("-",A2,FIND("/",A2))),"")

 

[kweaver]

Shoaib: amiroo's formulas assume there are always at most 2 sets of name/number combinations. Is that the case always?

 

[kweaver]

While it's a 2-step process, I found in ASAP Utilities (FREE) options to remove all numbers from a column and remove all text from a column.
After running those two options on copies of the initial data, I did a substitute of "-" for the names and a trim after a substitute of "-" on the IDs.
Works VERY nicely if you can get by with the add'l steps. And, it doesn't matter how many sets of name/IDs you have.

 

[Peter_SSs]

Perhaps this? (also handles several names/IDs)

ShoaibAli 2020-02-29 1.xlsm
	A	B	C
1	Name / Primary - ID / Secondary - ID	Name	Primary - ID / Secondary - ID
2	Kashif Khan - 526584944	Kashif Khan	526584944
3	Shoaib Ali - 25624621 / Jahanzain Rajput - 265565414	Shoaib Ali / Jahanzain Rajput	25624621 / 265565414
4	PVT - 111111111 / Kashif Khan - 526584944	PVT / Kashif Khan	111111111 / 526584944
5	Name 1 - 12365 / Name 2 - 89652145 / Name 3 - 562524 / Name 4 - 986524	Name 1 / Name 2 / Name 3 / Name 4	12365 / 89652145 / 562524 / 986524
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=TEXTJOIN(" / ",1,TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"-","/"),"/",REPT(" ",100)),SEQUENCE(,LEN(A2),,200),100)))
C2:C5	C2	=TEXTJOIN(" / ",1,TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"-","/"),"/",REPT(" ",100)),SEQUENCE(,LEN(A2),100,200),100)))

 

[kweaver]

Peter: that's a really slick use of the new SEQUENCE dynamic arrays function!

 

[Fluff]

Also cross posted Raw Data separation

 

[ShoaibAli]

Peter_SSs

It not working look.

Name / Primary - ID / Secondary - ID	Name	Primary - ID / Secondary - ID
Kashif Khan - 526584944	#NAME?	#NAME?​
Shoaib Ali - 25624621 / Jahanzain Rajput - 265565414	#NAME?	#NAME?
PVT - 111111111 / Kashif Khan - 526584944	#NAME?	#NAME?
Name 1 - 12365 / Name 2 - 89652145 / Name 3 - 562524 / Name 4 - 986524	#NAME?	#NAME?