SOLVED

Extract a date from a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2046782%22%20slang%3D%22fr-FR%22%3EExtract%20a%20date%20from%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046782%22%20slang%3D%22fr-FR%22%3E%3CP%3EI%20used%20an%20excel%20sheet%20to%20create%20flashcards.%3C%2FP%3E%3CP%3EIn%20each%20cell%2C%20there%20are%204%20%22lines%22%20(tabulated%20with%20Alt%20-%20Enter)%20and%20I%20need%20to%20select%20the%203rd%20%22line%22%2C%20exclusively%20made%20of%20numbers%3A%20a%20year%20(1550)%20or%20a%20range%20(1526-1533)%20to%20copy%20this%20data%20and%20then%20sort%20my%20file%20by%20date.%3C%2FP%3E%3CP%3EHow%20should%20I%20do%3F%20Process%2C%20functions%3F%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3EI%20attach%20a%20test-file%2C%20where%20I%20%3CU%3Eput%20two%20dates%20in%20red%3C%2FU%3E%20to%20help%20you%20see%20the%20information%20I%20want%20to%20copy%20and%20its%20format%2C%20but%20%3CU%3Edates%20are%20not%20in%20red%20in%20the%20original%20file.%3C%2FU%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2046782%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2046842%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20date%20from%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046842%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F923642%22%20target%3D%22_blank%22%3E%40Dominique74940%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20the%20SPLIT()%20function.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINDEX(FILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(A1%2CCHAR(10)%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%22)%2C3)%3C%2FCODE%3E%3C%2FPRE%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINDEX(FILTRE.XML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUE(A1%3BCAR(10)%3B%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%3B%22%2F%2Fz%22)%3B3)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2047709%22%20slang%3D%22fr-FR%22%3ERe%3A%20Extract%20a%20date%20from%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2047709%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3EVogelaar%20%40Hans%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3EI%20should%20have%20said%20that%20I%20work%20with%20excel%20in%20spanish%2C%20therefore%20I%20need%20to%20understand%20the%20actual%20meaning%20of%20the%20functions%20%3CSTRONG%3EMID%2C%20CHAR%2C%20REPT%2C%3C%2FSTRONG%3E%20%3CSTRONG%3E%22%20%2C%20%3C%2FSTRONG%3Eto%20translate%20them%20in%20excel%20spanish%20syntaxis%20in%20order%20to%20execute%20the%20formula.%20Currently%20ASIS%20it%20is%20not%20working.%3C%2FP%3E%3CP%3ELooking%20at%20SUBSTITUTE%2C%20I%20need%20to%20complete%204%20fields%3A%20text%2C%20original%20text%2C%20new%20text%20and%20no%20of%20repetitions%2C%20but%20I%20imagine%20that%20this%20would%20be%20solved%20by%20entering%20the%20whole%20formula%2C%20once%20translated%20into%20spanish%20the%20four%20forementioned%20functions.%3C%2FP%3E%3CP%3ESorry%20to%20be%20so%20dependent...%3C%2FP%3E%3CP%3EThank%20You.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2047926%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20date%20from%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2047926%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F923642%22%20target%3D%22_blank%22%3E%40Dominique74940%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20added%20both%20to%20the%20attached%20file%2C%20just%20open%20it%2C%20function%20will%20be%20in%20your%20locale%20notation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2051867%22%20slang%3D%22fr-FR%22%3ERe%3A%20Extract%20a%20date%20from%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2051867%22%20slang%3D%22fr-FR%22%3E%3CP%3EThank%20you%20very%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan!!%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New Contributor)
Solution

Merci infiniment@Sergei Baklan !!