Advance Excel Question only for experts?

Question

Hi, I'm in a middle of a riddle, i want to filter the content of a range of cells from the right and from the left, lets say for instance: http://www.myspace.com/mysite.asp and I only want myspace.com to stay alone.

The left part is easy cause its always the same "http://www." which is 11 characters so mid(a2,12,40) will always work.

But i don't know how to deal with the right part cause that part varies from a site to another, and I'm not sure if its possible to find a character like this "/" in myspace"/"mysite.asp, and stop counting.

Please help me out here, I'm out of options.

Answer 1

In VBA you can use the SPLIT function to break apart a string into array elements based upon a delimiter character(s)

Place the following code in to a button click event on your worksheet....

Dim str As String '
Dim AY() As String 'A string array for the split results

str = Me.Cells(1, 1) 'Cell contains "http://www.myspace......
AY = Split(str, "//") 'AY(0) will = "http:" AY(1) = "www.m....
Me.Cells(2, 1) = AY(1) 'Cell contains "www.myspace.com/....
str = Me.Cells(2, 1)
AY = Split(str, "/")
Me.Cells(3, 1) = AY(0) 'equals www.myspace.com

Answer 2

You have several flaws in your code. According to your note, I see the reason you are getting an answer that is Double and not an Integer. Here's the reason why: debtHolder = 0 debtHolder = Range("G" & column) debt = debt + debtHolder Notice that you are setting debtHolder equal to a range's value. Excel's default data type for numbers is Double. When you add an Integer (debt) to a Double (debtHolder), the answer it should give you is a Double. You really should specifiy debtHolder as an Integer data type. Also you have nothing in place to handle any possible errors that might come up like if one of the numbers isn't a number or an integer. You also are using too many parenthesis. Range doesn't really need surrounded with parenthesis. And I'd recommend replacing column with row. It makes more sense, because you are moving down to the next row and not moving over to the next column. Also your function arguments are a little confusing. I'm not sure why you have: Function CALLED(ParamArray list() As Variant) As Integer Did you leave a comma out? Should it be? Function CALLED(ParamArray, list() As Variant) As Integer I'm thinking you only need one of the following: Function CALLED(list() As Variant) As Integer or Function CALLED(ByVal list() As Variant) As Integer Although I would probably used the first one (ByRef) since the list is the Variant data type and would take up more space. Another thing I see that could be a problem with the code is that you don't specify an initial value for CALLED. Right after your declare your variables with your Dim statements you really should declare what the CALLED value is set to in case nothing is returned. There are also some ways you could speed up your macro big time by transferring Range A and Range E to arrays. You also have to be careful about using just the plain Value property for evaluation. A cell could possibly be formatted as text and have a number in it. If you use the value property, it will not evaluate that number as a number, but rather it will evaluate it as text because Excel adds an apostrophe to the front of it. It's a good idea to use IsNumeric to determine if the value is a number, then use the Val function to evaluate the value as a number. Then since you are looking at integers you probably should use the Mod operator to determine if a remainder other than zero is returned and the number isn't an Integer. You have several ways that your Function could result in an Error and the user should never have to deal with VBA errors. If you need some assistance feel free to email me. I'd be glad to help if I can be of assistance. PS - A good way to go about fixing your code is to print your variable values to the Immediate Window. The Immediate Window can be brought up in the VBE window by pressing (Ctrl + G). So if at some point in your code you want to find out what the debt variable equals, then type in: Debug.Print debt You might want to use an Exit Sub here or there to figure out where and why the variable is being set to the wrong value, so you can stop it at that point and evaluate the variable value. And yes you can do additions such as: column = column + 1 That is common practice in VBA coding. One last thing, use the Long data type instead of the Integer data type to specify the row number. It's not a good idea to use the integer type when referring to rows. Excel has over 65,000 rows and the Integer data type goes up to only 30,000 or so I think. The basic point is that the Integer data type won't go through all of the rows on the worksheet if you need it to. The Long data type is the data type that takes up the least amount of memory and will accomplish that task. For columns, you can use the Integer data type since there are only 256 columns in Excel.

Answer 3

http://www.regular-expressions.info/

Read, learn, apply. Regular Expressions (regex's) are extremely powerful and exactly the tool for what you're trying to use - essentially a language to describe string patterns. Excel is capable of employing regular expressions - just search the Help file for using them.

Answer 4

You can do the left part. First do that and select that column

Data
Text to coulmn
Delimited
Other "/"

Then you'll have the parts you want. But first remove the left part.

Answer 5

there are hundreds of options in several languages. try perls pattern matching capabilities.