Excel 2007 - Complex Formula Needed, I Think

[kjv1611]

I have a project I'm working on at my job where I am wanting to create an Excel workbook for reporting data to SharePoint via Excel Services. This idea was based upon another department doing the same thing, which seemed to work great for them. However, what I am wanting to do is a little more dynamic, as far as the results to be returned.

Initially, I did it rather easily (I think) in a VBA macro, returning a custom function. I now have learned that you cannot use custom functions (formulas) that run VBA code in Excel Services within SharePoint, SO.. that option is dead in the water.

All that said to say this. I would like to do this using standard, built-in formulas in Excel to get around this little speed bump. I'll work on it today and get what I can, but thought I'd post here to see if anyone else had any better ideas.

Here's the VBA code that worked (actually it ran fine on my PC, runing from within the local workbook, but did not work on 2 other PCs, all from the same workbook, same version of Excel - 2007):

Function Text_Divide(R As Range)
'Code Purpose: for wrapping the original text values into an easily readable format
'Author: Stephen Ferguson
'Date: Friday, August 7, 2009

On Error GoTo ExitFunction

    Dim strOrig() As String 'Dynamic Array for splitting the string
    Dim x As Integer 'Count splits in Original Text
    Dim strNew As String 'Temp holder for new string value
    Dim LF As String 'Line Break/Line Feed - Using same as <Alt> + <Enter>
    Dim B As String 'Bullet Point - so can give a bulleted List in one cell
        
    If R = vbNullString Then GoTo ExitFunction
    strOrig = Split(R, ";")
    
    LF = Chr(10) & Chr(10) 'Line Break/Line Feed - Using same as <Alt> + <Enter>
    B = Chr(149) 'Bullet Point - so can give a bulleted List in one cell
    
    For x = LBound(strOrig) To UBound(strOrig)
        If x = LBound(strOrig) Then
            strNew = B & " " & Trim(strOrig(x))
            Debug.Print "------------------------------------------------" & Chr(13) & _
                        "  " & x & Chr(13); "---" & Chr(13) & strNew & Chr(13)
        Else
            strNew = strNew & LF & B & " " & Trim(strOrig(x))
            Debug.Print "------------------------------------------------" & Chr(13) & _
                        "  " & x & Chr(13); "---" & Chr(13) & strNew & Chr(13)
        End If
    Next x
    
    Text_Divide = strNew

ExitFunction:
    x = 0
    strNew = vbNullString
    LF = vbNullString
    B = vbNullString
    Erase strOrig
    Exit Function
    
ErrHandle:
    On Error Resume Next
    GoTo ExitFunction
    
End Function

Please just ignore the use of the error handler for now, as I did mean to later update that once I knew the function would work. However, I found it won't, so I'm not concerned about the error handler - I would handle it a little better if it really did need it.

I did previously ALMOST have it working like I wanted. The only part I haven't gotten sorted out is handling the issue of different numbers of delimiters. I suppose that will have to be handled with IIF formulas, and possibly by using a few cells for diff steps of the process. If there is some other way to handle dynamic possibilities of the number of delimiters, I'd really like to know about it!

[blue]********************************************
Here's the text of what I'm trying to do:
I've got strings of text based on different States. The user inputs the State code into one cell, and the text strings are returned from their values based on that cell.. so VLOOKUP is what I'll use, unless there's a better way for that piece. I can return straight text, that's no problem.

Then what I want to do is to divide the text into an easily readable format, rather than something like:
Text Part One; Text Part Two; Text Part Three; Text Part Four

To do that, I was easily able to handle all possibilities via the custom function. But now I've got to get the same results via standard Excel formulas. I've got some ideas, but I'm hoping someone will have a simpler suggestion than what I'll probably end up doing.
********************************************[/blue]

Thanks for any advice/suggestions/examples/references. I hope to have it put into formula format, myself, today or tomorrow at latest. That's not to rush anyone else, but to say that I'm hoping I'll have it all sorted out, regardless of whether anyone else chimes in or not.

I'll post back as soon as I come up with anything myself. If nothing else, hopefully it'll provide a good learning experience for myself and anyone else interested in reading/thinking about it.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

I haven't done so yet, but I do have one thought that has come to my mind so far... just can't test it out yet, b/c working on other matters.

What if I have one cell return the number of [blue];[/blue] characters from the original text, and then the other formula works based on that...

Well, at least it's a start?

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

I forgot to post back when I came to a resolution. I don't want to take the time to document the whole thing, but basically for now, I've got the thing working by using a few different formulas in different cells.

--

"If to err is human, then I must be some kind of human!" -Me