Como retornar tudo entre dois caracteres no Excel?

Question

Sou um programador com muitas linguagens, mas de alguma forma nunca aprendi a linguagem de fórmulas ou macros do Excel. Tenho certeza que deve haver uma maneira simples de fazer isso, mas estou perplexo.

Eu tenho uma coluna de nomes de arquivos com esta aparência:

/home/www/shoppermarketexpo/dot/uploads/10DOT_33A_1275_1308304857_1.jpg
/home/www/shoppermarketexpo/dot/uploads/10DOT_15G_1184_1.jpg
/home/www/shoppermarketexpo/dot/uploads/10DOT_5A_1450_1.jpg
/home/www/shoppermarketexpo/dot/uploads/10DOT_18A_1341_1308325044_2.jpg

O que eu preciso fazer é retornar apenas o nome do arquivo, dividido em várias partes com base no sublinhado. Estou bem se isso exigir várias etapas - ou seja, a fórmula 1 retorna uma coluna com 10DOT_33A_1275_1308304857 , e a fórmula 2 usa esse resultado para retornar uma segunda coluna com 10DOT_33A_1275 e assim por diante. Eu estava esperando que houvesse uma função semelhante à explosão do PHP, onde você pode pegar uma string e retornar um array baseado em um delimitador (neste caso, o sublinhado). Eu estou supondo que isso não é possível na fórmula; Eu ficaria bem com o VBA, apesar de estar em um Mac e não saber se está disponível para mim.

Eu sei sobre MID, LEFT e RIGHT, mas todos eles dependem de você saber o número de caracteres que deseja retornar e, neste caso, não sei. Varia de linha para linha; o único critério que funciona é dividir o nome do arquivo em várias colunas nos sublinhados.

Isso é possível?

    

Answer 1

Você pode usar a funcionalidade Text to Columns do Excel no menu Data.

Vai levar vários passes, mas você pode fazer isso sem fórmula ou vba.

Você pode especificar seus próprios delimitadores. Assim, você pode escolher . para separar a extensão e, em seguida, / para dividir a estrutura de diretórios. Então você pode escolher _ para separar o nome do arquivo.

Você pode definir as diferentes colunas como Text para evitar que o Excel as trate como números ou defina outras colunas como Do not import (skip) para que sejam removidas.

    

Answer 2

E aqui está uma solução baseada em VBA. Note que o loop através das células como este não é muito eficiente. Se você tem muitas linhas (digamos mais que algumas 10.000), existem maneiras melhores

Selecione o intervalo que você deseja processar e execute este

Sub SplitText()
    Dim rng As Range, rw As Range
    Dim cl As Range
    Dim i As Long, j As Long, k As Long
    Dim str As String

    If Not TypeName(Selection) = "Range" Then Exit Sub

    Set rng = Selection
    For Each rw In rng.Rows
        str = rw.Cells(1, 1)
        str = Mid(str, InStrRev(str, "/") + 1)
        str = Left(str, InStr(str, "."))
        j = InStr(str, "_")
        k = 2
        Do While j > 0
            rw.Cells(1, k) = Left(str, j - 1)
            str = Mid(str, j + 1)
            j = InStr(str, "_")
            k = k + 1
        Loop
        rw.Cells(1, k) = str
    Next
End Sub

    

Answer 3

Aqui está uma solução baseada em fórmulas:

Supondo que os dados estão na coluna A, começando na linha 2 e há no máximo 5 peças a serem devolvidas (podem ser ajustadas para lidar com mais). e que as cordas não contêm | ou @ (se o fizerem, use alguns outros caracteres)

Célula B2, obtém o nome do arquivo =RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2,"/","|",LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))))

A célula C2 remove a extensão. =LEFT(B2,FIND(".",B2)-1)

Células D2: H2 como uma fórmula de matriz, agrupa termos sucessivos com | e @ @ =SUBSTITUTE(SUBSTITUTE("_"&C2&"_","_","|",{1,2,3,4,5}),"_","@",{1,2,3,4,5})

Célula I2 e, em seguida, copiar I2 para J2: L2: o resultado de =IFERROR(MID(D2,FIND("|",D2)+1,FIND("@",D2)-FIND("|",D2)-1),"")

    

Answer 4

Há uma função Dividir no VBA que ajudará você com seu plano, mas não há nenhuma fórmula SPLIT.

No entanto, sugiro usar uma fórmula do Excel. Alguns casos:

1. Se o caminho do arquivo for o mesmo para cada arquivo, use o DIREITO juntamente com LEN e FIND, desta forma:

   = DIREITA (A1, LEN (A1) -FIND ("uploads /", A1) -LEN ("uploads /") + 1)

   em que A1 é a célula com o caminho do arquivo.

2. Se todos os arquivos não tiverem o mesmo caminho, você terá que usar outra coisa. Talvez todos os arquivos sejam .jpg (?) Ou não tenham períodos, exceto antes da extensão do arquivo.

Answer 5

Como não há resposta selecionada ainda, darei a você outra opção VBA, mas desta vez com a função split.

    Option Explicit

Sub SplittySplit()

Dim fileLocCell As Range
Dim fileName As String
Dim fileNameBeginsAt As Integer

Dim fileNameSubParts() As String
Dim fileNameExtensionLoc As Integer

Dim rng As Range
Set rng = Selection 'or some more specific rnage
    For Each rw In rng.Rows
        Set fileLocCell = Cells(7, 4)
        fileNameBeginsAt = InStrRev(fileLocCell.Text, "/")
        fileName = Right(fileLocCell, (Len(fileLocCell.Text) - fileNameBeginsAt))
        'if you don't want the ".jpg" at the end or whatever the extension maybe***
        fileNameExtensionLoc = InStrRev(fileName, ".")
        '***
        fileName = Left(fileName, fileNameExtensionLoc - 1)
        fileNameSubParts = Split(fileName, "_") 'an array indexed at 0

        'do whatever you want with the array
        Dim i As Integer
        For i = 0 To UBound(fileNameSubParts)
            Cells(i + 1, 1) = fileNameSubParts(i)
        Next i
    Next rw

End Sub