Formula to Extract Data

[punit83]

Pls help me with formula for extracting data according to criteria.
I want to extract data from column A to column B, C, D respectivly.
Pls note sometime there are 2 or more space in data

Book1
	A	B	C	D
1	ORDER DETAIL	CLIENT	ORDER #	DATE
2	CLIENT : MCDOWEL ORDER # 7259 DATE : 01/07/2024
Sheet1

Thanks in Advance.
Punit

 

[hagia_sofia]

Could this work for you:

=LEFT(MID(TRIM(A2),10,99),FIND(" ORDER",MID(TRIM(A2),10,99)))

=--(LEFT(MID(TRIM(A2),FIND(" # ",TRIM(A2))+3,99),FIND(" ",MID(TRIM(A2),FIND(" # ",TRIM(A2))+3,99))))

=--(MID(TRIM(A2),FIND("DATE : ",TRIM(A2))+7,99))

 

[punit83]

Could this work for you:

=LEFT(MID(TRIM(A2),10,99),FIND(" ORDER",MID(TRIM(A2),10,99)))

=--(LEFT(MID(TRIM(A2),FIND(" # ",TRIM(A2))+3,99),FIND(" ",MID(TRIM(A2),FIND(" # ",TRIM(A2))+3,99))))

=--(MID(TRIM(A2),FIND("DATE : ",TRIM(A2))+7,99))

Hello,

Thanks a lot, it worked but can you pls guide formula for data as below, i need for another excel file.
Thanks one more time.

ORDER #7259 MCDOWEL 10-06-2024

ORDER #927 DCWEL 06/06/2024

I tried to make little changes in your above mention formula but i am getting error.
You will be my hour saver, thanks

 

[Cubist]

Try

Book1
	A	B	C	D
1		Order	Name	Date
2	ORDER #7259 MCDOWEL 10-06-2024	7259	MCDOWEL	10-06-2024
3	ORDER #927 DCWEL 06/06/2024	927	DCWEL	06/06/2024
Sheet5

Cell Formulas
Range	Formula
B2:B3	B2	=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),FIND("#",SUBSTITUTE(A2," ",REPT(" ",99)))+1,99))
C2:C3	C2	=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),FIND(B2,SUBSTITUTE(A2," ",REPT(" ",99)))+LEN(B2)+1,99*2))
D2:D3	D2	=MID(A2,FIND(C2,A2)+LEN(C2)+1,99)

 

[punit83]

Try

Book1
	A	B	C	D
1		Order	Name	Date
2	ORDER #7259 MCDOWEL 10-06-2024	7259	MCDOWEL	10-06-2024
3	ORDER #927 DCWEL 06/06/2024	927	DCWEL	06/06/2024
Sheet5

Cell Formulas
Range	Formula
B2:B3	B2	=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),FIND("#",SUBSTITUTE(A2," ",REPT(" ",99)))+1,99))
C2:C3	C2	=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),FIND(B2,SUBSTITUTE(A2," ",REPT(" ",99)))+LEN(B2)+1,99*2))
D2:D3	D2	=MID(A2,FIND(C2,A2)+LEN(C2)+1,99)

Thanks Thanks A Lot......................