SOLVED

Extract a date from a cell

Copper Contributor

I used an excel sheet to create flashcards.

In each cell, there are 4 "lines" (tabulated with Alt + Enter) and I need to select the 3rd "line", exclusively made of numbers: a year (1550) or a range (1526-1533) to copy this data and then sort my file by date.

How should I do? Process, functions?

Many thanks

I attach a test-file, where I put two dates in red to help you to see the information I want to copy and its format, but dates are not in red in the original file.

5 Replies

@Dominique74940 

With a formula:

Enter the following formula in B1:

 

=SUBSTITUTE(MID(SUBSTITUTE(A1,CHAR(10),REPT(CHAR(10),255)),450,255),CHAR(10),"")

 

Fill down.

Sort on column B.

 

With Text to Columns:

Select column A.

Click Data > Text to Columns.

Select Delimited, then click Next >.

Tick the Other check box, then click in the box next to it and press Ctrl+J.

Click Next >.

Select 'Do not import column (skip)' for all columns except the one with the years.

Select Text for the years column.

Make sure that you select B1 (or a cell in an empty column) as Destination (otherwise, you'll overwrite column A!)

Click Finish.

Sort on column B.

@Dominique74940 

Use the SPLIT() function. :)

 

=INDEX(FILTERXML("<y><z>"&SUBSTITUTE(A1,CHAR(10),"</z><z>")&"</z></y>","//z"),3)
=INDEX(FILTRE.XML("<y><z>"&SUBSTITUE(A1;CAR(10);"</z><z>")&"</z></y>";"//z");3)

 

 

@Hans Vogelaar 

Thank you!

I should have said that I work with excel in spanish, therefore I need to understand the actual meaning of the functions MID, CHAR, REPT, » , to translate them in excel spanish syntaxis in order to execute the formula. Currently ASIS it is not working.

Regarding SUBSTITUTE, I need to complete 4 fields: text, original text, new text and nº of repetitions, but I imagine that this would be solved by entering the whole formula, once translated into spanish the four forementioned functions.

Sorry to be so dependant...

Thank you.

@Dominique74940 

I added both to the attached file, just open it, function will be in your locale notation.

best response confirmed by Dominique74940 (Copper Contributor)
Solution

Merci infiniment@Sergei Baklan !!

1 best response

Accepted Solutions
best response confirmed by Dominique74940 (Copper Contributor)
Solution