Fórmulas do Excel para remover o prefixo / sufixo em torno do número

Question

No outro dia, eu tinha algumas informações de quadro de horários em uma planilha ($ X / h, Y hrs) e queria calcular os dólares gastos. Acabei de editar os caracteres "/ hr", "hrs" e "$", mas existe alguma maneira de eu ter convertido essas células diretamente em floats na fórmula para executar o cálculo?

= B $ 3 * B $ 4

Eu tentei VALUE e não funcionou.

    

Answer 1

Experimente esta pequena UDF

Public Function NumberPart(s As String) As Double
    Dim s2 As String, i As Long, L As Long, CH As String
    s2 = ""
    L = Len(s)
    For i = 1 To L
        CH = Mid(s, i, 1)
        If CH Like "[0-9]" Or CH = "." Then
            s2 = s2 & CH
        End If
    Next i
    NumberPart = CDbl(s2)
End Function

Funções Definidas pelo Usuário (UDFs) são muito fáceis de instalar e usar:

1. ALT-F11 exibe a janela do VBE
2. ALT-I ALT-M abre um novo módulo
3. cole o material e feche a janela do VBE

Se você salvar a pasta de trabalho, a UDF será salva com ela. Se você estiver usando uma versão do Excel posterior a 2003, deverá salvar o arquivo como .xlsm em vez de .xlsx

Para remover o UDF:

1. abrir a janela do VBE como acima
2. limpe o código
3. feche a janela do VBE

Para usar o UDF do Excel:

= NumberPart (A1)

Para saber mais sobre macros em geral, consulte:

link

e

link

e

link

para detalhes sobre UDFs

As macros devem estar ativadas para que isso funcione!

    

Answer 2

Embora a resposta acima funcione e esteja bem documentada, tenho duas objeções contra ela:

• Fornece saída falsa se a entrada contiver valores numéricos não intencionais (por exemplo, tente: "12 hrs1" ou "$ 12/1 hr" ou quando sua localidade usar "," como um separador decimal em vez de ".") .
• Se possível, eu ficaria longe do Visual Basic for Applications (VBA) e do UDF; especialmente se o mesmo puder ser alcançado com as seguintes funções nativas do Excel:
  • LEFT : leva o lado esquerdo de uma string até um determinado comprimento (usado para cortar o "/ hr").
  • VALUE : converte um tekst em um valor (usado para converter o valor restante em uma string em um valor).
  • FIND : procura por um caractere ou string em outra string (usada para procurar por '/' em "/ hr").
  • SUBSTITUTE : altera todas as ocorrências de uma string em outra string (usada para remover o "HRS").
  • UPPER : converte todos os caracteres para maiúsculos (usados para alterar "Hrs" e "hrs" etc. para "HRS").

Se os valores mencionados estiverem na coluna A (preço por hora) e B (quantidade de horas), começando com dados na linha 1 (sem linha de cabeçalho), coloque as seguintes fórmulas no Excel:

• C1: =VALUE(LEFT(A2;FIND("/";A1;1)-1)) e copie e cole conforme necessário.
• D1: =VALUE(SUBSTITUTE(UPPER(B1);"HRS";"")) e copie e cole conforme necessário.

Isso deve fazer o truque. Nada muito complexo eu diria.

    

Answer 3

Outro método é usar o VBA junto com o Regex e um padrão de ajuste .
Por exemplo, o padrão \D corresponde a tudo o que não é um dígito (0-9).

1. Insira este código em um módulo do VBA ( ALT + F11 ). O UDF precisa de 2 argumentos quando chamado do Excel: A célula de entrada e o padrão RegEx para verificação. Em seguida, o código remove todos os caracteres que correspondem ao padrão especificado.

   Function Remove(objCell As Range, strPattern As String)
   
       Dim RegEx As Object
       Set RegEx = CreateObject("VBScript.RegExp")
       RegEx.Global = True
       RegEx.Pattern = strPattern
       Remove = RegEx.Replace(objCell.Value, "")
   
   End Function
   

2. De volta ao Excel, escreva sua fórmula nesta sintaxe para uma nova célula

   =Remove( <cellToCheck> ,"<regExPattern>")
   

Tomei o seu exemplo para multiplicar duas células "não dígitos": $50/hr * 8 hrs = 400

No entanto, isso não funcionará se você tiver vários valores numéricos (não intencionais) em uma célula, por exemplo, $50/hr2