CODEWITHSUNDEEP
excelexcel-formulaopenoffice-calc
NRGdallas
NRGdallas

Reputation: 395

Dynamically pull first, last, words from an excel/openoffice cell (and then some!)

Cell contents:

Epson/Epson TM Series Supplies/H5000;Epson/Epson TM Series Supplies/H5200;Epson/Epson TM Series Supplies/U590;Epson/Epson TM Series Supplies/930;Epson/Epson TM Series Supplies/U925;Epson/Epson TM Series Supplies/U950

I need to pull a resulting cell with the following rules: The first word, the last word before any semicolons, repeating, subbing the semicolon for comma. there could be 4, 27, or 0 semicolons (or any number in between)

the above field would result in an output of Epson H5000, Epson H5200, Epson U590, Epson 930, Epson U925, Epson U950

insane amount of bonus points and props if the resulting values were alphabetically sorted somehow IE: Epson 930, Epson H5000, Epson H5200, Epson U590, Epson U925, Epson U950

Column A is about 28000 values long. Results in column B are fine.

If anybody has any ideas on where to even begin tackling this problem, I have run out of thoughts on it.

Upvotes: 0

Views: 457

Answers (1)

We Are All Monica
We Are All Monica

Reputation: 13354

I'm assuming that you can't easily get your data in another format. That would be the best solution.

I'm also assuming that your "words" are always separated by slashes. In Excel, I would write a VBA function for this task. Something like this:

Option Explicit

Function GetPrinterModels(r As Range) As String
    Dim arr() As String, arr2() As String
    arr = Split(r.Value, ";")

    Dim results() As String
    ReDim results(0 To UBound(arr))

    Dim i As Integer
    For i = 0 To UBound(arr)
        arr2 = Split(arr(i), "/")
        ' If words can also be separated by spaces:
        'arr(i) = Replace(arr(i), "/", " ")
        'arr2 = Split(arr(i), " ")
        results(i) = arr2(0) & " " & arr2(UBound(arr2))
    Next

    GetPrinterModels = Join(results, ", ")
End Function

As far as sorting, you're on your own. You want to sort the results array before calling Join on it in the last line of the function. Sadly, VBA doesn't have a built-in Sort function, but there are tons of ready-made sort functions available online.

Upvotes: 1

Related Questions