Forum Discussion
Extract a date from a cell
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.
Merci infinimentSergeiBaklan !!
5 Replies
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)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.
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.
I added both to the attached file, just open it, function will be in your locale notation.
