Reverse the order of words in a cell
Solved
senecartour
Posted messages
391
Status
Membre
-
senecartour Posted messages 391 Status Membre -
senecartour Posted messages 391 Status Membre -
Hello,
I would like to reverse the order of the words in the cells of a column
For example: Paris, 75019, avenue de Flandre, 82
Becomes
82, avenue de Flandre, 75019, Paris
Thank you for your response,
--
Sharing knowledge is the best way to learn!
I would like to reverse the order of the words in the cells of a column
For example: Paris, 75019, avenue de Flandre, 82
Becomes
82, avenue de Flandre, 75019, Paris
Thank you for your response,
--
Sharing knowledge is the best way to learn!
1 réponse
Hello
If the addresses are always presented as city-comma-address-comma-number
With an address in A1 in B1 put the formula:
=RIGHT(A1;MATCH(",";MID(A1;LEN(A1)-ROW($1:$255);1);0)) &SUBSTITUTE(SUBSTITUTE(A1;RIGHT(A1;MATCH(",";MID(A1;LEN(A1)-ROW($1:$255);1);0));"");LEFT(A1;FIND(",";A1)-1);"")&" "&LEFT(A1;FIND(",";A1)-1)
Note, array formula to be validated by CTRL+SHIFT+Enter (the formula then automatically gets enclosed in { } )
Then drag down to the bottom of the column (double click on the black square in the bottom right of B1)
Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein
If the addresses are always presented as city-comma-address-comma-number
With an address in A1 in B1 put the formula:
=RIGHT(A1;MATCH(",";MID(A1;LEN(A1)-ROW($1:$255);1);0)) &SUBSTITUTE(SUBSTITUTE(A1;RIGHT(A1;MATCH(",";MID(A1;LEN(A1)-ROW($1:$255);1);0));"");LEFT(A1;FIND(",";A1)-1);"")&" "&LEFT(A1;FIND(",";A1)-1)
Note, array formula to be validated by CTRL+SHIFT+Enter (the formula then automatically gets enclosed in { } )
Then drag down to the bottom of the column (double click on the black square in the bottom right of B1)
Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein
It’s really kind of you.
Have a good day!