Formula to extract data

[howard]

Attached please find sample data

1) I need a formula in O1497 to extract the first value after LN in A1497: for eg 107,006.18-
2) I need a formula in U1497 to extract the month and day that appears after the first value for eg 04/19
3) I need a formula in V1497 to extract the last value in A1497 for eg 1,692,886.38

Your assistance in this regard will be most appreciated

Statements.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1497	FNAOS000049566LN:107,006.18-04/191,692,886.38
1498	FNAOS000049567LN:662.70-04/191,692,223.68
Table 1

 

[JoeMo]

Try this after you change the cell references to fit your sheet:

Excel Workbook
	A	B	C	D
1	FNA OS 000049566 LN: 107,006.18- 04/19 1,692,886.38	107,006.18-	04/19	1,692,886.38
Sheet7

 

[howard]

Hi Joe

Thanks for the help. Have now learnt how to combine "mid,left & right with search functionality.

I need some further help. I found some data that is slightly diffrent, where I need to extract the same information i.e the first value after the first section of text, then the month/day and then final value.

Statement Sample.xltm
	A	B	C	D	E	F	G
1	FNAOB000039573EQK10,000.00-04/19329,559.32
2	FNAOB000039574EQG91,576.61-04/19237,982.71
3	FNAOATRFTOCORPORA8,600.00-04/19229,382.71
4	FNAOATRFFROMNPRI118,416.9404/19347,799.65
5	FNAOATRFTOCTNI/C500,000.00-04/19152,200.35-
6	FNAOATRFFROMCALL600,000.0004/19447,799.65
Sheet1

 

[JoeMo]

This seems to work (copy formulas shown down):

Excel Workbook
	A	B	C	D
1	FNA OB 000039573 EQ K 10,000.00- 04/19 329,559.32	10,000.00-	04/19	329,559.32
2	FNA OB 000039574 EQ G 91,576.61- 04/19 237,982.71	91,576.61-	04/19	237,982.71
3	FNA OA TRF TO CORPORA 8,600.00- 04/19 229,382.71	8,600.00-	04/19	229,382.71
4	FNA OA TRF FROM NPR I 118,416.94 04/19 347,799.65	118,416.94	04/19	347,799.65
5	FNA OA TRF TO CTN I/C 500,000.00- 04/19 152,200.35-	500,000.00-	04/19	152,200.35-
6	FNA OA TRF FROM CALL 600,000.00 04/19 447,799.65	600,000.00	04/19	447,799.65
Sheet7

 

[howard]

Hi Joe

Thanks for the help, much appreciated