0

As you can see in the image below, I have a column with values like this:

10/1/1399

11/1/1399

23/2/1400

16/12/1390

1/1/1400

and I want to re-order them to be like this:

1399/1/10

1399/1/11

1400/2/23

1390/12/16

1400/1/1

Sheet Image

is there a way to do that either on MS Excel or Google Sheet?

Mehran Goodaz
  • 9
  • 3

5 Answers5

2

Within sheets, you can also try:

=INDEX(REGEXREPLACE(TO_TEXT(A2:A6),"(.*)/(.*)/(.*)","$3/$2/$1"))

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
1

Try:

=ARRAYFORMULA(BYROW(QUERY(SPLIT(A1:A,"/"),"select Col3, Col2, Col1"),LAMBDA(row,TEXTJOIN("/",1,row))))
z''
  • 4,527
  • 2
  • 3
  • 12
1

Assuming input is actual text, representing a date, two options that worked for me in GS:

enter image description here

Formula in B1, results may be locale settings dependent:

=INDEX(TEXT(DATEVALUE(QUERY(A:A,"Where A is not null")),"yyyy/m/d"))

Formula in C1:

=INDEX(REGEXREPLACE(QUERY(A:A,"Where A is not null"),"(\d+)(\/\d+\/)(\d+)","$3$2$1"))

For Excel, the 1st option won't work because it's a date prior to 1-1-1900 (AFAIK). What could work is:

=MAP(TOCOL(A:A,1),LAMBDA(x,TEXTJOIN("/",,INDEX(TEXTSPLIT(x,"/"),{3,2,1}))))
JvdV
  • 70,606
  • 8
  • 39
  • 70
1

In Excel:

=BYROW(A2:INDEX(A:A,COUNTA(A:A)),LAMBDA(r,TEXTJOIN("/",,INDEX(TEXTSPLIT(r,"/"),{3,2,1}))))

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • 1
    Nice, alternatively, may I suggest `=MAP(DROP(TOCOL(A:A,1),1),LAMBDA(....` in case of a header =) – JvdV Feb 03 '23 at 14:32
  • That's good, I hadn't seen that use of tocol with the second parameter to remove blanks. – Tom Sharpe Feb 03 '23 at 17:04
  • BTW the timings for both versions seem to be about the same - running it on a full column is much slower as expected. – Tom Sharpe Feb 03 '23 at 17:14
0

the most efficient way would be to make a macro that does this as a one column operation, but the easiest way I could think to do it is to separate the cell into columns delimited by the slash, and then concatenate them in reverse order and add the slashes back.

corgs
  • 1