6

I'm a programmer with lots of languages, but somehow I've never learned Excel macro or formula language. I'm sure there must be a simple way to do this, but I'm stumped.

I have a column of file names that look like this:

/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

What I need to do is return just the file name, split into several parts based on the underscore. I'm fine if this takes multiple steps - i.e. formula 1 returns a column with 10DOT_33A_1275_1308304857, then formula 2 uses that result to return a second column with 10DOT_33A_1275 and so on. I was hoping there'd be a function similar to PHP's explode, where you can take a string and return an array based on a delimiter (in this case, the underscore.) I'm guessing that isn't possible in formula; I'd be OK with VBA, although I'm on a Mac and don't know if it's available to me.

I know about MID, LEFT, and RIGHT, but all of them depend on you knowing the number of characters you want to return, and in this case I don't. It varies from row to row; the only criteria that works is splitting the file name into multiple columns at the underscores.

Is this possible?

3
  • I suggest you post this in Microsoft's Excel forum. You should be able to find someone who can help you whip up a VBA scipt .
    – surfasb
    Jul 1, 2011 at 20:11
  • 1
    @surfasb - thanks, but you need a Windows Live account to post in MS forums, and my company blocks access to signing up for one.
    – EmmyS
    Jul 1, 2011 at 20:30
  • Oh my bad. I just know this is easy in C#, but I'm not a VBA guy. And I hardly ever see any of them running around here.
    – surfasb
    Jul 1, 2011 at 22:47

5 Answers 5

4

You could use Excel's Text to Columns functionality under the Data menu.

It will take several passes but you can do it without formula or vba.

You can specify your own delimiters. So you can choose . to separate the extension, then / to split out the directory structure. Then you can choose _ to split apart the file name.

Text to Columns Wizard

N.b. You can set the different columns to Text to avoid Excel treating them as numbers, or set other columns to Do not import (skip) so they are removed.

1
  • I'm accepting this one because it appears that my Mac version of office does not come with VBA. But I will keep the other answers in mind for future reference; they're all good.
    – EmmyS
    Jul 12, 2011 at 13:40
6

And here's a VBA based solution. Note, looping through cells like this is not very efficient. If you have a lot of rows (say more than a few 10,000's), there are better ways

Select the range you want to process and run this

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
5

Here's a formula based solution:

Assuming data is in column A, starting row 2
and there are at most 5 pieces to be returned (can be adjusted to cope with more)
and that the strings do not contain | or @ (if they do, use some other characters)

Cell B2, gets the file name =RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2,"/","|",LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))))

Cell C2, removes the .extension
=LEFT(B2,FIND(".",B2)-1)

Cells D2:H2 as an array formula, brackets successive terms with | and @
=SUBSTITUTE(SUBSTITUTE("_"&C2&"_","_","|",{1,2,3,4,5}),"_","@",{1,2,3,4,5})

Cell I2, and then copy I2 to J2:L2: the result
=IFERROR(MID(D2,FIND("|",D2)+1,FIND("@",D2)-FIND("|",D2)-1),"")

3

Since there's no selected answer yet I'll give you another VBA option but this time with the split function.

    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
1
  • oh there was a selected answer...well have this anyway :)
    – Brad
    Jul 12, 2011 at 14:12
2

There is a Split function in VBA that would help you with your plan, but there is no SPLIT formula.

However, I suggest using an Excel formula instead. A couple of cases:

  1. If the file path is the same for each file, just use RIGHT together with LEN and FIND, like this:

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

    where A1 is the cell with the file path.

  2. If all files don't have the same path, then you have to go by something else. Maybe all the files are .jpg (?) or don't have periods except for before the file extension.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .