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?

Please Login or Register  to view this content.

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!

what bothers me about it is that it works but it projects the results in the column right next to it.

How do I change this program so that I can choose which column do the results go?

I am too spoiled. I should start hitting the books but I have so much on my mind and my conscience keeps telling me "ask Tony... Ask Tony..."

Hi,
I'm using Excel 2003. If you can open the xls with 2003, run the Macro “SplitTest” on the attached file should get you the result.

I’ll try to explain the code a little bit.

X = Split(“Blah bb/gg 2/3/4”,”/”) will result
X(0)= “Blah bb”
X(1)= “gg 2”
X(2) = “3”
X(3) = “4”

Use InStrRev() and Right() to change x(1) to “2”
Output X(1)~X(3) to column C,D,E

Hope this helps!
Tony

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)

Please Login or Register  to view this content.

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.

Just one caveat.
The code includes a 'Split' command. Unfortunately this command isn't part of the VBA set for Microsoft Excel 2004 for Mac.

Seems it's only available in the Windows version.

Rgds

Mac ?
If you only want Split function, there's a code I wrote for xl95, however I don't think you can set the reference to WSH RegExp anyway.

Bad luck.