Page 1 of 1
Text cleaning
Posted: Fri Aug 16, 2024 6:58 am
by sna
Hi there
I need your input how to write formula to split date ,product and quantity apart
I attach a template
Thanks
Re: Text cleaning
Posted: Fri Aug 16, 2024 12:03 pm
by puriwutpokin
D2=LET(s,SORT(TEXTSPLIT(SUBSTITUTE(B2,CHAR(32),""),","),,-1,TRUE),CHOOSECOLS(HSTACK(SORT(DROP(--s,,1),,-1,TRUE),TAKE(s,,1)),1,3,2)) Copy down
Re: Text cleaning
Posted: Fri Aug 16, 2024 1:03 pm
by sna
thanks it works.how to make it spill range?
Re: Text cleaning
Posted: Fri Aug 16, 2024 2:52 pm
by norkaz
...
D2
=LET(b,B2:B8,a,MID(b,SEARCH("/",b)-4,10),c,TOCOL(MID(SUBSTITUTE(SUBSTITUTE(b,a,""),",",REPT(" ",50)),{1,50,100},50)+0,3),HSTACK(a+0,TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(b,a,""),c,""),",","")),c))
Bigcat9
Bangkok Thailand
Re: Text cleaning
Posted: Mon Aug 19, 2024 8:55 am
by sna
thanks so much