string processing

Hi!

Let's say I have a table (one column):

Item name
Blah bb/gg 2/3/4
Hlab ji/lk 10/50
Shlim 67/pp 45/56-90

The script/macro/I-don't-know-what would fill out this table (I added three new column):
Item name Width Height Length
Blah bb/gg 2/3/4 2 3 4
Hlab ji/lk 10/50 10 50 0
Shlim 67/pp 45/56-90 45 56-90 0

Basically, what it does is that it looks for the '/' char and recognizes the strings around it. It skips the first two strings from the right (they are a part of the name) and records the next 3 strings which are usually numbers (or like "56-90") and places them each into his own cell.

The task I have is to take these "Item names" and extract the size data out of them, for example:
Hlab ji/lk 10/50 is an item called hlab ji/lk with measurements 10 by 50 by 0 and each of these measurements has to go to it's own cell for each line.

If it's too much for you to just reveal to me as a ready solution, I would appreciate any direction on how I can make a solution.

Thanks. Tod.

Hi,

Could you put these strings in column A in worksheet, and in columns to the right enter the strings you expect to see. Then upload the workbook to the forum. It's always easier if we can see the problem in context.

Rgds

Hi,
Something like this?

Public Sub SplitTest()
Dim lR
Dim i
Dim j
Dim k
Dim x
lR = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lR
    Cells(i, 2).Resize(1, 3).Value = 0  'Init next 3 columns with 0
    x = Split(Cells(i, 1).Value, "/")
    For j = 1 To UBound(x)
        If j = 1 Then
            k = InStrRev(x(j), " ")
            If k <> 0 Then
                Cells(i, j + 1).Value = Right(x(j), Len(x(j)) - k)
            Else
                Cells(i, j + 1).Value = x(j)
            End If
        Else
            Cells(i, j + 1).Value = x(j)
        End If
    Next j
Next i
End Sub

Tony

I am surprised of your willingness to help.
Here's a pic if you are not willing to open an Excel file made by someone else
or if I screwed up something:
http://img180.imageshack.us/img180/3015/picaz5.jpg

Blue is the input data, green is the automatically generated form.

Tell you the truth, I can do it manually but I would really like to learn Excel and help other just as much as you are helping me so your efforts are in fact going to help this community in the long run.

Tony, I am new to Excel and I just switched to 2007 which made things even worse. Could you tell me what to do with that code?

Tony, I played around a little and actually got something useful!
I think if I play some more I can get something absolutely working!

Hi,

See attached workbook.

Rgds

It does help a lot. In the short run, I can get the job done and in the long run I have a base on which I can develop more skills.

I am still not entirely fluent with the code but maybe it's because I didn't even start learning VBA?

SOLVED.

T H A N K S

Hi!

My brother wants to know
1. What happens if the source data is formed like:
Blah bb/gg 2/3/4 Blah
It doesn't work in that case, neither the elegant Macro nor the Uber Haxx Excel cell formula.
2. Can a macro be made that checks the source data and deletes the entries will less than 3 '/'s?

Working practically 24/7 this month. I wish I had time to experiment with it at home.

UDF
select C3:E3 and type =Mustey(A3)
then confirm with Ctrl + Shift + Enter (array formula entry)

Function Mustey(txt As String) As Variant
Dim temp
With CreateObject("VBScript.RegExp")
    .Pattern = "\d+/\d+(\-\d+)?(/\d+(\-\d+)?)?"
    temp = Split(.execute(txt)(0), "/")
    Mustey = Array(temp(0), temp(1), 0)
    If UBound(temp) > 1 Then Mustey(2) = temp(2)
End With
End Function

Thank you very much Jindon. Please let me know how do I use that code you have so kindly provided me? I have no idea where to enter it.