Solved: function to retrieve characters between to given values

[danlu]

Hi,

I am looking for a function that will retrieve all characters (within a cell) between two by-the-user-defined input parameters.

For example if I have in a cell the text:
(interesting way)

I would like to be able to write a function like
function_name("(";")") ,ie a function where I define that all characters between my to input parameters, in this example "(" and ")" ,should be retrieved. So the result showing upp should be: interesting way
that is, all characters between the two input parameters were retrieved (NOT including the input parameters themselves).

[Bob Phillips]

Function GetChars(rng As Range, startChar As String, endChar As String)
Dim iStart As Long
Dim iEnd As Long

If rng.Count > 1 Then
GetChars = CVErr(xlErrRef)
Exit Function
End If
iStart = InStr(rng.Value, startChar)
iEnd = InStr(rng.Value, endChar)
If iStart = 0 Or iEnd = 0 Or iEnd <= iStart Then
GetChars = CVErr(xlErrValue)
Exit Function
End If
GetChars = Mid$(rng.Value, iStart + 1, iEnd - iStart - 1)
End Function

[unmarkedhelicopter]

Bob, Just in case the delimeters are together ?
Function GetChars(rng As Range, startChar As String, endChar As String)
Dim iStart As Long
Dim iEnd As Long
If rng.Count > 1 Then
GetChars = CVErr(xlErrRef)
Exit Function
End If
iStart = InStr(rng.Value, startChar)
iEnd = InStr(rng.Value, endChar)
If iStart = 0 Or iEnd = 0 Or iEnd <= iStart Or iStart + 1 = iEnd Then
GetChars = CVErr(xlErrValue)
Exit Function
End If
GetChars = Mid$(rng.Value, iStart + 1, iEnd - iStart - 1)
End Function

Damn ! the VBA quotes are hard to work with, indenting is an art !!!

[Bob Phillips]

I cater for them being contiguous, nothing returned, and being the same place, #VALUE.

[mikerickson]

danlu,

What do you want returned from "ab(cd(efg)hi)jk"?

"ab(cd)ef(gh)ij"?

"ab(cd)ef)gh"

[mikerickson]

Might this do what you want?
Function innerString(inputString As String, leftEnd As String, rightEnd As String) As String
innerString = Split(inputString, leftEnd)(UBound(Split(inputString, leftEnd)))
innerString = Split(innerString, rightEnd)(LBound(Split(innerString, rightEnd)))
End Function

[danlu]

Hi,

Been away for some days, sorry for late reply.
Thanks to all of you , your solutions works perfectfly! When speaking about extracting data from a string, is there some function that do the same as the inbuilt MID function, but instead of starting from left it starts from right in a string?

For ex if you have in a cell: (Sweden is a country)
and you want to extract: is a country
you would like to find the second character from the right and then starting from this second character extract 12 characters (including blanks).

[Bob Phillips]

=MID(A1,LEN(A1)-2-12,12)