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!

 

[Akira181]

do the yellow numbers differ in length? i.e. is it always 3 numbers before the date or always 8 after?

If both sets of yellow numbers differ in length every time, then I don't believe it's possible to automate

 

[Joe4]

Welcome to the Board!

It might be best to show a few more examples, trying to capture the different formats that we may see, and indicate the values you want to return for each one.
Otherwise you may get an answer that works for the one example you posted, but not on all your data.

 

[Anaya Zeeshan]

If the numbers in yellow are always of same length, then this should work:
=MID(A1,26,4)

 

[Naresha]

Welcome to the Board!

It might be best to show a few more examples, trying to capture the different formats that we may see, and indicate the values you want to return for each one.
Otherwise you may get an answer that works for the one example you posted, but not on all your data.

Thank you Joe, I'm glad to be a member of the board!

Sure, a detailed example is as follows:

First and second columns are system generated. so the transaction date is date when payment hit the bank and the date in the middle of the line is the date the payment belongs to, that I need to extract in the third column titled "date".

NB: dates for the description "Journal - Credit Card " remains unchanged.

Transactiondate	Description	Date
12/04/2021​	Journal - Credit Card (33061) -	12/04/2021​
13/04/2021​	Elavon Financial S EMS397041222543401	12/04/2021​
13/04/2021​	Journal - Credit Card (33061) -	13/04/2021​
14/04/2021​	Elavon Financial S EMS398041322423401	13/04/2021​
14/04/2021​	Elavon Financial S EMS393041323063402	​
14/04/2021​	Journal - Credit Card (33061) -	​
15/04/2021​	Elavon Financial S EMS394041422413402	​
15/04/2021​	Journal - Credit Card (33061) -	​
16/04/2021​	Elavon Financial S EMS400041523303401	​

 

[Naresha]

do the yellow numbers differ in length? i.e. is it always 3 numbers before the date or always 8 after?

If both sets of yellow numbers differ in length every time, then I don't believe it's possible to automate

Hi Akira,

Thank you for your response

Yes, both set of yellow numbers remain same in length, only the value is changed, please refer to my above response for a detailed example of the scenario.

regards,
Naresha

 

[Joe4]

So, are the values in the third column for the "Journal..." entries already populated with hard-coded values?
Or are they just pulling the first column via formula?

Are these two types of records the only ones that you will have, so all records with ALWAYS begin with either:
"Journal - Credit Card..."
or
"Elavon Financial S EMS..."

 

[Joe4]

If my assumptions in the previous post are correct, and the values entered in the first column are actually entered as dates and not text, try entring this formula in cell C2 and copying down for all rows:

=IF(LEFT(B2,7)="Journal",A2,DATE(YEAR(A2),MID(B2,26,2),MID(B2,28,2)))

 

[Naresha]

If my assumptions in the previous post are correct, and the values entered in the first column are actually entered as dates and not text, try entring this formula in cell C2 and copying down for all rows:

=IF(LEFT(B2,7)="Journal",A2,DATE(YEAR(A2),MID(B2,26,2),MID(B2,28,2)))

Hi Joe,

That's correct, values in first column are entered as date. However, I have tried inputting your formula suggest above, it seems to work for the description line with journal but not for the cells with description "Elavon Fianacial S". Note sure if I was meant to change any number or char reference, please advise on the results obtained in column titled "formula cells" using this formula.

Transactiondate	Description	Formula cells
12/04/2021​	Journal - Credit Card (33061) -	12/04/2021​
13/04/2021​	Elavon Financial S EMS397041222543401	09/05/2024​	not picking correct date
13/04/2021​	Journal - Credit Card (33061) -	13/04/2021​
14/04/2021​	Elavon Financial S EMS398041322423401	19/05/2024​	not picking correct date
14/04/2021​	Elavon Financial S EMS393041323063402	30/03/2024​	not picking correct date
14/04/2021​	Journal - Credit Card (33061) -	14/04/2021​
15/04/2021​	Elavon Financial S EMS394041422413402	09/04/2024​	not picking correct date
15/04/2021​	Journal - Credit Card (33061) -	15/04/2021​
16/04/2021​	Elavon Financial S EMS400041523303401	31/03/2024​	not picking correct date

 

[Joe4]

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)