Texto Excel para converter formato de data

Question

Eu tenho um lote de dados com várias colunas cuja data está formatada assim:

31 de maio de 2014.

Estou tentando descobrir uma maneira de converter isso em um formato de data útil 31/05/2014. Alguma ideia?

Answer 1

Existem algumas boas etapas para conseguir isso. Essa é uma maneira de fazer isso usando fórmulas e células auxiliares. Se você precisar fazer isso sem células auxiliares (ou seja, não deseja colunas extras), mencione isso e provavelmente eu poderia criar algum VBA para realizar a tarefa.

A primeira tarefa é dividir sua data em 3 partes: o ano, o mês, o dia. Depois de ter esses três, é simples juntá-los novamente.

É claro que isso pressupõe que suas datassempresiga o formato de data americano bastante idiota © de MMM-dd-aaaa.

Isso foi feito usando a página da MicrosoftDivida o texto entre colunas usando funçõescomo um exemplo.

1. Dividindo o mês:
   Isso é feito usando a searchfunção para encontrar o primeiro -e a ferramenta de divisão Leftpara cortá-lo.

   =LEFT(A1, SEARCH("-",A1,1)-1)

   que retorna Maydo seu exemplo.

2. Dividir o dia:
   É semelhante, mas é necessário um uso muito mais complicado da MIDfunção. Precisamos encontrar o primeiro e o segundo -e depois subtrair a localização do primeiro do segundo para obter o comprimento do texto necessário. Este resultado é uma função bastante longa:

   =MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)

   the SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)retorna o comprimento enquanto the SEARCH("-",A1,1)+1está simplesmente obtendo o primeiro local. a MIDferramenta os corta e retorna 23do seu exemplo.

3. Dividindo o ano:
   A última ferramenta que precisamos é a RIGHTferramenta.

   =RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1))

   Isso simplesmente obtém o comprimento da string e, em seguida, pesquisa duas vezes e retorna tudo o que está à esquerda (ou à direita).

O problema é que agora você ainda tem o mês em formato de texto inutilizável. Para torná-lo utilizável, precisamos usar a DATEVALUEferramenta para convertê-lo. Esta ferramenta não aceita um mês sozinha, mas aceita uma MMM YYstring. O que podemos fazer é analisar o texto do mês em um número depois de concatenarmos um ano nele.

4. Obtendo o mês como um valor numérico:
   Para fins de argumentação, digamos que armazenamos a primeira função na célula B1.
   DATEVALUE(B1 &" 01")retornará a string de valor de data que o Excel usa internamente, podemos então agrupá-la em uma MONTHfunção para retornar o valor numérico do mês:

   MONTH(DATEVALUE(B1 &" 01"))

5. Podemos então mesclar tudo isso em uma DATE(year,month,day)função e, assim, convertê-la em uma data real que o Excel possa usar. O mês está na célula B1, o dia na célula C1 e o ano na célula D1 seguindo o Stupid American Date Format™:

   DATE(D1, B1, C1)

   retornará então 23/05/2014na célula.

6. Se você quiser ser realmente sofisticado, pode combinar todas as fórmulas acima em uma.

=DATE(RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1)),MONTH(DATEVALUE(LEFT(A1, SEARCH("-",A1,1)-1)&"  1")),MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1))

Answer 2

Outra abordagem simples é pegar uma string como31 de maio de 2015e convertê-lo em31 de maio de 2015. Esta conversão nos permitirá usar oDATAVALOR()Fórmula.

Então, com dados emA1, emB1digitar:

=DATEVALUE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","") & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))

Answer 3

Sei que a pergunta foi respondida e agradeço todas as contribuições. Gostaria de adicionar esta outra opção, caso seja útil para alguém:

=DATEVALUE(CONCATENATE(MID(A1,5,2),"-",LEFT(A1,3),"-",RIGHT(A1,4)))

Isso converterá a data do formato 31 de maio de 2014 para o formato 31/05/2014. Observe que no meu caso específico os meses foram inseridos com apenas três letras (por exemplo, julho)

Obrigado!