extract date from a line with texts and numbers

[Naresha]

Good afternoon!

I wonder if there is a way to extract date from the below line, numbers in red is the date (ie 18/10) that needs to be extracted and numbers in yellow are variables that differ in each line but the texts in each line remains the same. Please Help!

Elavon Financial S EMS589101823113401

TIA!

 

[Naresha]

I am thinking that maybe you need to adjust a formula, and didn't do it quite right, because it works for me.

Can you confirm what columns you actual data is in, and what row the data starts on?
What cell are you putting this formula in?

Are you absolutely sure the values in the first column are valid date entries, and not dates entered as text?
You can check easily with a formula. Let's say that your first date is in cell A2. Then pick any blank cell and enter this formula and see what it returns:
=ISNUMBER(A2)
If it returns TRUE, you have a valid date. If it returns FALSE, you do not.

Also, are there any leading spaces at the beginning of your Descriptions?
If your first "Elavon" description is in cell B3, enter this formula in a blank cell and tell me what it returns:
=MID(B3,26,4)

excel copy.xlsx
	A	B	C	D	E	F	G
1	Transactiondate	Description	Formula cells
2	12/04/2021	Journal - Credit Card (33061) -	12/04/2021		TRUE
3	13/04/2021	Elavon Financial S EMS397041222543401	09/05/2024	not picking correct date	3970
4	13/04/2021	Journal - Credit Card (33061) -	13/04/2021
5	14/04/2021	Elavon Financial S EMS398041322423401	19/05/2024	not picking correct date
6	14/04/2021	Elavon Financial S EMS393041323063402	30/03/2024	not picking correct date
7	14/04/2021	Journal - Credit Card (33061) -	14/04/2021
8	15/04/2021	Elavon Financial S EMS394041422413402	09/04/2024	not picking correct date
9	15/04/2021	Journal - Credit Card (33061) -	15/04/2021
10	16/04/2021	Elavon Financial S EMS400041523303401	31/03/2024	not picking correct date
11
12
13
14
15
16
Sheet2

Cell Formulas
Range	Formula
E2	E2	=ISNUMBER(A2)
E3	E3	=MID(B3,26,4)
C2:C10	C2	=IF(LEFT(B2,7)="journal",A2,DATE(YEAR(A2),MID(B2,26,2),MID(B2,28,2)))

 

[Naresha]

I am thinking that maybe you need to adjust a formula, and didn't do it quite right, because it works for me.

Can you confirm what columns you actual data is in, and what row the data starts on?
What cell are you putting this formula in?

Are you absolutely sure the values in the first column are valid date entries, and not dates entered as text?
You can check easily with a formula. Let's say that your first date is in cell A2. Then pick any blank cell and enter this formula and see what it returns:
=ISNUMBER(A2)
If it returns TRUE, you have a valid date. If it returns FALSE, you do not.

Also, are there any leading spaces at the beginning of your Descriptions?
If your first "Elavon" description is in cell B3, enter this formula in a blank cell and tell me what it returns:
=MID(B3,26,4)

Hi Joe,

I have tried to upload a mini-sheet for this working, hope this helps to figure out the error.

regards,
Naresha

 

[Joe4]

A ha! This is what happens when we are not provided accurate examples. Compare the last image you posted to the previous ones.
This current one show multiple spaces in the string before the "EMS..." part. Your previous ones only show single spaces. I think copy/pasting directly gets rid of mutliple spaces between items, which is why it it typically not a good tool to post sample data.

So there are three extra spaces that need to be accounted for in the formula, i.e.
=IF(LEFT(B2,7)="Journal",A2,DATE(YEAR(A2),MID(B2,29,2),MID(B2,31,2)))

 

[Naresha]

A ha! This is what happens when we are not provided accurate examples. Compare the last image you posted to the previous ones.
This current one show multiple spaces in the string before the "EMS..." part. Your previous ones only show single spaces. I think copy/pasting directly gets rid of mutliple spaces between items, which is why it it typically not a good tool to post sample data.

So there are three extra spaces that need to be accounted for in the formula, i.e.
=IF(LEFT(B2,7)="Journal",A2,DATE(YEAR(A2),MID(B2,29,2),MID(B2,31,2)))

Yay, this works! thank you so much Joe.. you are indeed a genius
And my apologies for not providing accurate examples in first place but I appreciate your patience and now can see how quickly I can finish my task using your formula.x

 

[Joe4]

You are welcome.
Glad we got it all sorted out and working for you!