No Excel 2007, preciso converter um número de telefone em um número de série, preservando os últimos 4 dígitos

Question

Exemplo: suponha que o número de telefone do dispositivo seja 847-630-4459 .

• Etapa 1: adicionamos um "1" à frente do número que gera: 1-847-630-4459
• Passo 2: Para os primeiros 7 dígitos (1-847-630), subtraímos cada um de 10. Por exemplo. 10-1 = 9, 10-8 = 2, etc.
• Etapa 3: o novo rótulo é criado como um "número de série". O resultado final ficaria assim:

  S/N 92634704459

Eu preciso fazer isso por 465 linhas telefônicas - existe uma maneira de criar uma fórmula para fazer isso automaticamente?

    

Answer 1

Supondo que todos os seus números tenham 10 dígitos, em seguida, com número em A1, tente esta fórmula em B1 para converter conforme necessário

="S/N 9"&SUMPRODUCT(MOD(-MID(SUBSTITUTE(A1,"-",""),{1,2,3,4,5,6},1),10),10^{5,4,3,2,1,0})&RIGHT(A1,4)

Você pode copiar a fórmula na coluna para converter seu conjunto completo de números

    

Answer 2

Aqui está uma abordagem mais matematicamente orientada:

="S/N 9"&(1111110-INT(SUBSTITUTE(A1,"-","")/10000))*10000+MOD(SUBSTITUTE(A1,"-",""),10000)

Infelizmente, ele não implementa suas instruções exatamente - ele é desativado em um no dígito anterior quando o número de telefone de entrada contém 0 (zero). Mas satisfaz o seu requisito implícito, ou seja, que números de telefone diferentes devem sempre produzir números de série diferentes.

E, claro, você pode reduzir um pouco essa fórmula usando uma coluna auxiliar. Se você definir B1 para conter =SUBSTITUTE(A1, "-", "") , o número de série poderá ser expresso como

="S/N 9" & (1111110-INT(B1/10000))*10000 + MOD(B1, 10000)