Forum Discussion

Brass Contributor

Nov 07, 2022

Solved

Trying to embed special characters into referenced values

Hello All, I am trying to fetch values from another sheet columns and not able to append special characters like "('%name%') in between values. In the given below formula , my value coming from a...

excel

SnowMan55

Nov 23, 2022

AlokBeheria That work is certainly doable using a custom VBA function. In fact, I wrote two. Put either (or both) of the functions below into a standard code module (i.e., where macros are located). To reduce visual clutter, I used an ellipsis (...) in place of some of the text. Here are samples of the Excel formulas and results on some data I made up:

For the first function (AlokBeheria1), I wrote the code to do "exactly" the requested work:

Public Function AlokBeheria1(ByVal DelimitedList As String _
        , Optional ByVal Delimiter As String = ",") As Variant
'   This function parses a delimited list of entries, and wraps the
'       second entry, plus all others, within another set of strings.

    Dim vntReturnValue  As Variant
    Dim strReturnValue  As String
    
    Dim strEntries()    As String
    Dim in4UpperBound   As Long
    Dim in4Index    As Long
    Dim strRemainingEntriesAsList   As String
    
    '----   Parse the delimited list into an array of strings.
    strEntries = Split(DelimitedList, Delimiter)
    in4UpperBound = UBound(strEntries)
    If in4UpperBound < 1 Then
        '...the list does not have two entries.
        vntReturnValue = CVErr(5)
        GoTo AB1_Exit
    End If
    
    '----   Construct the desired output.
    '   Create the first part.
    strReturnValue = "... -preferred " & strEntries(1)  'Entry #2 is _
            referenced by index 1, because the array is zero-based.
    '   Create a new delimited list of entries with all but the
    '   second entry.
    strRemainingEntriesAsList = strEntries(0)
    For in4Index = 2 To in4UpperBound
        strRemainingEntriesAsList = strRemainingEntriesAsList _
                & Delimiter & strEntries(in4Index)
    Next in4Index
    '   Put the pieces together.
    strReturnValue = strReturnValue & " -a " & strRemainingEntriesAsList
    '
    vntReturnValue = strReturnValue
    
AB1_Exit:
    '----   Return the result.
    AlokBeheria1 = vntReturnValue
    Exit Function
    
End Function

For the function's argument, specify a cell containing the delimited list (or a string literal; e.g., =AlokBeheria1("A,B,C") ).

But that function has hard-coded values, so you would have to create a separate custom function for each different set of string literals for which you wanted to use the code. And it is always the second entry in the delimited list that gets special treatment.

Alternatively, you could use the second function (DelimitedListEntry) -- along with built-in Excel functions and operators -- for this purpose as well as for other purposes:

Public Function DelimitedListEntry(ByVal DelimitedList As String _
        , ByVal EntryNum As Integer _
        , Optional ByVal Delimiter As String = ",") As Variant
'   This function parses a delimited list of entries, and returns
'       either the entry whose ordinal position number is EntryNum (if
'       EntryNum is positive) or a modified delimited list that excludes
'       entry number EntryNum*-1 (if EntryNum is negative).

    Dim vntReturnValue  As Variant
    Dim strReturnValue  As String
    
    Dim strEntries()    As String
    Dim in4UpperBound   As Long
    Dim in4Index    As Long
    Dim strRemainingEntriesAsList   As String
    
    '----   Do some validation of the parameters.
    If EntryNum = 0 Then
        vntReturnValue = CVErr(5)
        GoTo DLE_Exit
    End If
    
    '----   Parse the delimited list into an array of strings.
    strEntries = Split(DelimitedList, Delimiter)
    in4UpperBound = UBound(strEntries)
    
    '----   If a single entry is requested, return it.
    If EntryNum > 0 Then
        If EntryNum - 1 <= in4UpperBound Then
            strReturnValue = strEntries(EntryNum - 1)
            vntReturnValue = strReturnValue
        Else
            vntReturnValue = CVErr(5)
        End If
        GoTo DLE_Exit
    End If
    
    '----   Create a new delimited list of entries with all but the
    '       specified entry.
    If EntryNum = -1 Then
        '   For improved performance, handle this case separately.
        strReturnValue = Mid$(DelimitedList, 1 + Len(strEntries(0)) _
                + Len(Delimiter))
    Else
        '   Create the first part.
        strRemainingEntriesAsList = strEntries(0)
        '   Append additional entries as appropriate.
        For in4Index = 1 To in4UpperBound
            If in4Index <> Abs(EntryNum) - 1 Then
                strRemainingEntriesAsList = strRemainingEntriesAsList _
                        & Delimiter & strEntries(in4Index)
            End If
        Next in4Index
        '   Put that into the String return value.
        strReturnValue = strRemainingEntriesAsList
    End If
    vntReturnValue = strReturnValue
    
DLE_Exit:
    '----   Return the result.
    DelimitedListEntry = vntReturnValue
    Exit Function
    
End Function

For the function's first argument, specify a cell with the delimited list (or a string literal). For the second argument, specify a positive number to return just a single entry whose (1-based) position is that number; or if you specify a negative number, the function returns the list excluding the single entry whose position is the absolute value of that number.

Note: For both functions, I allow a delimiter other than a comma to be specified. as the last argument.

I expected DelimitedListEntry to be much slower, as Excel has to make references to twice as many other cells, has to invoke the function twice, and has to do the concatenation itself. But the execution times were reasonably close for this and for AlokBeheria1.

mathetes

Gold Contributor

Nov 12, 2022

I'm sorry; I really am not able to follow what you're trying to do. The picture you have doesn't have headings, which is part of the issue, but it's also just the way you've described it. It would help with clarity if you could write your desires in separate bullet sentences or numbered sentences like I did in my very first reply to your first question.

AlokBeheria

Brass Contributor

Nov 14, 2022

mathetes

My Apologies for that .. Let me try explaining this to you with an example .. I have a 2 sheets called sheet 1 and sheet 2 .. In sheet 1 values marked in Red being referenced using "&Sheet1!B12&" and "&Sheet1!B12&" . I had no issue and it was just replacing values using cell reference. I figured out problem while referring row 9 cluster names as these values are separated by comma and I need those to be displayed in some order like "T0002082 -a T0002081,T0002083". In row 10 , values start from T0002081,T0002082,T0002083 and now I need to adjust these according to the output I described refer Sheet2 refer sheet 2 row B. Formula I used is = "srvctl add service -db $DBNAME -service "&Sheet1!B12&" -pdb "&Sheet1!B13&" -preferred T0002082 -a T0002081,T0002083". Hope , I was able to clarify now.
Sheet 1

Sheet 2

Data

Information	Details
EAI Number / Name	Test123
Database Level	L6
Source Cluster(s)	LAC-CT-ISA
Source Service OUD	LAC_ISA_SVC1_L4
Source PDB Name	LAC_ISA_PDB_L4
Source Connection string(s)	jdbc:oracle:thin:@ldap://eusoud.prod.fedex.com/LAC_ISA_SVC1_LVL4,cn=OracleContext
Source schema (s) / App	LAC_ISA_SCHEMA LAC_ISA_APP
Cluster Names	T0002081,T0002082,T0002083
New PDB Names	CTISA_PERM_01_LAC_L1
New Service Name	CTISA_PERM_01_LAC_L1_S1
New OUD/OID	CTISA_PERM_01_LAC_L1_S1

AlokBeheria
Brass Contributor
Dec 20, 2022
Just to update , I managed make it work the way I wanted and just in case , anyone has a similar query can use following solution to achieve the same.

Regards
AlokBeheria
Brass Contributor
Dec 09, 2022
Hello SnowMan ,One last thing, if you could help me on. Here, I am trying to concatenate csv values inside cell. I am looking to use concatenate function on values Val1, Val2, Val3 and it should be like 'Val1','Val2','Val3'. I tried to use concatenate function to no avail. Using concatenation, it treats entire CSV as single value, and I just couldn't be able to find ways to put "quotes" around CSV values.

Regards
SnowMan55
Bronze Contributor
Dec 06, 2022
AlokBeheria No apology was necessary. We all have priorities that change and unexpected demands on our time. Good to see that once you removed the extraneous ampersand in front of the B14 cell reference that you got your solution.
AlokBeheria
Brass Contributor
Dec 06, 2022
Just to update here, I managed to sort this out with following command.

Regards
AlokBeheria
Brass Contributor
Dec 06, 2022
Thanks once again, as I was able to make it work. Here, what I am trying to do is to use this formula inside " " in formula bar and not sure, why it's refused to work. I have changed the function name from AlokBeheria1 to clustname here.

Using cell referencing to append values inside double quotes.

Formula I am using ..

srvctl add service -db $DBNAME -service FSD_PERM_01_S1 -pdb FSD_PERM_01_DOTCOM &clustname(&sheet_modify_First!B14)
AlokBeheria
Brass Contributor
Dec 05, 2022
I just didn't have any clue on it and extremely I feel sorry for not commenting on a master piece of a work. I was crazy busy in some of my projects and couldn't able to check. First of all , thanks a million, I just casually looked today and discover your post.

I really have no words to praise you here. I am speechless here. you did a wonderful job .. Although I am yet to test and comprehending a function created by you , but will let you know for sure.

Once again thanks you and thanks to Mathetes as well. You both deserve a praise here. will post on my thoughts ..

Regards

SnowMan55

Bronze Contributor

Nov 23, 2022

For the first function (AlokBeheria1), I wrote the code to do "exactly" the requested work:

Public Function AlokBeheria1(ByVal DelimitedList As String _
        , Optional ByVal Delimiter As String = ",") As Variant
'   This function parses a delimited list of entries, and wraps the
'       second entry, plus all others, within another set of strings.

    Dim vntReturnValue  As Variant
    Dim strReturnValue  As String
    
    Dim strEntries()    As String
    Dim in4UpperBound   As Long
    Dim in4Index    As Long
    Dim strRemainingEntriesAsList   As String
    
    '----   Parse the delimited list into an array of strings.
    strEntries = Split(DelimitedList, Delimiter)
    in4UpperBound = UBound(strEntries)
    If in4UpperBound < 1 Then
        '...the list does not have two entries.
        vntReturnValue = CVErr(5)
        GoTo AB1_Exit
    End If
    
    '----   Construct the desired output.
    '   Create the first part.
    strReturnValue = "... -preferred " & strEntries(1)  'Entry #2 is _
            referenced by index 1, because the array is zero-based.
    '   Create a new delimited list of entries with all but the
    '   second entry.
    strRemainingEntriesAsList = strEntries(0)
    For in4Index = 2 To in4UpperBound
        strRemainingEntriesAsList = strRemainingEntriesAsList _
                & Delimiter & strEntries(in4Index)
    Next in4Index
    '   Put the pieces together.
    strReturnValue = strReturnValue & " -a " & strRemainingEntriesAsList
    '
    vntReturnValue = strReturnValue
    
AB1_Exit:
    '----   Return the result.
    AlokBeheria1 = vntReturnValue
    Exit Function
    
End Function

For the function's argument, specify a cell containing the delimited list (or a string literal; e.g., =AlokBeheria1("A,B,C") ).

Alternatively, you could use the second function (DelimitedListEntry) -- along with built-in Excel functions and operators -- for this purpose as well as for other purposes:

Public Function DelimitedListEntry(ByVal DelimitedList As String _
        , ByVal EntryNum As Integer _
        , Optional ByVal Delimiter As String = ",") As Variant
'   This function parses a delimited list of entries, and returns
'       either the entry whose ordinal position number is EntryNum (if
'       EntryNum is positive) or a modified delimited list that excludes
'       entry number EntryNum*-1 (if EntryNum is negative).

    Dim vntReturnValue  As Variant
    Dim strReturnValue  As String
    
    Dim strEntries()    As String
    Dim in4UpperBound   As Long
    Dim in4Index    As Long
    Dim strRemainingEntriesAsList   As String
    
    '----   Do some validation of the parameters.
    If EntryNum = 0 Then
        vntReturnValue = CVErr(5)
        GoTo DLE_Exit
    End If
    
    '----   Parse the delimited list into an array of strings.
    strEntries = Split(DelimitedList, Delimiter)
    in4UpperBound = UBound(strEntries)
    
    '----   If a single entry is requested, return it.
    If EntryNum > 0 Then
        If EntryNum - 1 <= in4UpperBound Then
            strReturnValue = strEntries(EntryNum - 1)
            vntReturnValue = strReturnValue
        Else
            vntReturnValue = CVErr(5)
        End If
        GoTo DLE_Exit
    End If
    
    '----   Create a new delimited list of entries with all but the
    '       specified entry.
    If EntryNum = -1 Then
        '   For improved performance, handle this case separately.
        strReturnValue = Mid$(DelimitedList, 1 + Len(strEntries(0)) _
                + Len(Delimiter))
    Else
        '   Create the first part.
        strRemainingEntriesAsList = strEntries(0)
        '   Append additional entries as appropriate.
        For in4Index = 1 To in4UpperBound
            If in4Index <> Abs(EntryNum) - 1 Then
                strRemainingEntriesAsList = strRemainingEntriesAsList _
                        & Delimiter & strEntries(in4Index)
            End If
        Next in4Index
        '   Put that into the String return value.
        strReturnValue = strRemainingEntriesAsList
    End If
    vntReturnValue = strReturnValue
    
DLE_Exit:
    '----   Return the result.
    DelimitedListEntry = vntReturnValue
    Exit Function
    
End Function

Note: For both functions, I allow a delimiter other than a comma to be specified. as the last argument.

mathetes
Gold Contributor
Nov 23, 2022
I've not been able to come up with a legacy function. The one I was thinking of only produces a single row. I think you need to check whether you can update to the most current, which would enable the formula I wrote.
AlokBeheria
Brass Contributor
Nov 23, 2022
OK, no problem. hope there may be some legacy function to achieve this. Thanks ..

Regards
mathetes
Gold Contributor
Nov 21, 2022
AlokBeheria

It seems to me that it should be supported by your Excel; that looks like the Windows version of what I have in my Mac. If you've not updated it to the most current, you should do so. In the meantime, let me see if there's a way within "legacy" functions to do the same thing.

As to whether VBA could do it, I am not the person to ask. I don't write VBA routines, preferring to find ways using the built-in functions. My guess is that, yes, one could write a VBA routine, but I'll defer to those qualified to do that.
AlokBeheria
Brass Contributor
Nov 21, 2022
mathetes

Thanks a ton for your help . Greatly Appreciated !! . It seems the version is not supported by excel. Following is the version of excel, but it seems, it's not supporting the formula.
Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20704) 64-bit.
I did bit of a researched and wondering if VBA can be used to implement same kind of a functionality?
Once again thanks a million for continues support.

Regards
mathetes
Gold Contributor
Nov 20, 2022
AlokBeheria

OK, here it is. But as I warned, this formula will require that you have the most current version of Excel. I'm attaching the spreadsheet where this has been set up to work.
First, this is what's in cell A1:
T0002081,T0002082,T0002083

And this is the formula in B1

=LET(spltArry,TEXTSPLIT(A1,","),"-preferred "&INDEX(spltArry,2)&" -a "&INDEX(spltArry,1)&","&INDEX(spltArry,3))

And that formula displays this way
If you prefer, for some reason, to include that text that I've considered spurious, then you can change that first string to read this way.

=LET(spltArry,TEXTSPLIT(A1,","),"This is the test and I want value like -preferred "&INDEX(spltArry,2)&" -a "&INDEX(spltArry,1)&","&INDEX(spltArry,3))
mathetes
Gold Contributor
Nov 20, 2022
AlokBeheria

I"ve been away from my home for a few days. So I'll see if I can get back into this. I do know that you've made it more complicated than it originally sounded, and so I realize I need to research some of the new text manipulating functions before giving a really useful answer.

You're going to need the most current version of Excel. Do you know what version you have?
AlokBeheria
Brass Contributor
Nov 18, 2022
Any suggestions from your side Friend? I am still not able to figure it out as how can these CSV values be used to create command. Thanks for your help ..

Regards
AlokBeheria
Brass Contributor
Nov 15, 2022
mathetes :- Thanks for being patience as it seems I broke the sequence initially just to get things simpler and in order to achieve it, I eventually ended up making it more confusing. let me explain according to your questions.

>> "Because C3 picks one value (T0002082) out of the middle of the three values in B3, and surrounds it with -preferred before and -an after. But it still also contains the other two remaining values from B3. (As well as being preceded by that phrase which I think is superfluous, "This is a test and I want values like" )"
Yes, I need other 2 values as well and these values should come after "-a" like "-a "T0002081,T0002083"

>> Do you want to designate one of the three values in B3 for special treatment.
Yes. like the above screen shot,"T0002082" is picked up from these 3 values and appended after "preferred"
If so, is it always the one in the middle?
>> Yes.
If so, what about all the other things in column B in your earlier examples that are altogether different?
>> I just tried to overexplain there and it and you can consider the screen shot for final one which I need.
>> And so forth.
Yes, again, it seems I tried too hard.

I want to use values in column B1, B2 and B3 to frame the formula and just for the sake of simplicity, I will skip the complete formula which I provided in my earlier post and can use values from B1 and B2 to frame according to my requirement.

My main worry was to shuffle around values from B3 and use them first with "-preferred" and then with "-a". The values from C3 cell are what I need and Cell B1 and B2, is not a problem for me and I am not referring them here because I can use them in a formula by simply referring them. The given syntax is not the actual formula, but it is just to help make you understand of my problem here. I know it eventually made my query look more complex than it otherwise would have been. Hope this time I managed to make you understood.

text values

New PDB name CTISA_PERM_01_LAC_L1
New Service Name CTISA_PERM_01_LAC_L1
Cluster Names T0002081,T0002082,T0002083 This is the test and I want value like like -preferred T0002082 -a T0002081,T0002083

Regards
mathetes
Gold Contributor
Nov 15, 2022
AlokBeheria

Well, you do need to explain further.

Because C3 picks one value (T0002082) out of the middle of the three values in B3, and surrounds it with -preferred before and -an after. But it still also contains the other two remaining values from B3. (As well as being preceded by that phrase which I think is superfluous, "This is a test and I want values like" )

So it's still not clear:
Do you want to designate one of the three values in B3 for special treatment?
If so, is it always the one in the middle?
If so, what about all the other things in column B in your earlier examples that are altogether different?
And so forth.
I really can't keep on asking for clarification. You need to BE clear. Your very data seem scrambled, incoherent (i.e., not following a discernible pattern)..... So maybe stepping back to describe the business context here: WHAT is it that the data represent in the first place? What are you doing with it? What's the ultimate output from all this?
AlokBeheria
Brass Contributor
Nov 15, 2022
I probably feel that I am trying to overexplain and may be that is leading to the confusion. In my screen shot, C3 is my desired values which I want using the values from B3 cell. My purpose is to generate formula similar to values displayed in C3 cell. I will not explain further and see if that helps you understand better.

Regards
mathetes
Gold Contributor
Nov 14, 2022
AlokBeheria
If we were sitting face to face, I'm sure we could sort through this and understand each other, but my sense is that you are trying too hard to explain something that is actually fairly simple, and your explanations get in the way of simple understanding.

What is the purpose of the 'This is a test and I want values like" string in all this? It's not really part of what you want, is it? It would appear that you think it's making things clearer, but it only adds to confusion, at least in my mind.

In your image there's a difference between C3 and C4; it's not clear which of them is the desired goal. Either way, you seem quite comfortable with string manipulation and concatenation, which also adds to the confusion. What are you NOT able to do that we haven't covered in some of the earlier exchanges?
AlokBeheria
Brass Contributor
Nov 14, 2022

OK , no problem .. let me try a another way .. I used only one sheet just to help understand as what I like to achieve here. In the below screen shot , Column C3 contains only texts and while using the formula values should be similar to C4. I used part of formula just to tell you that what adjustment I need to make or using different formula to arrive values similar to C3 "This is test and I want values like -preferred T0002082 -a T0002081,T0002083" . I want to use cell reference here , but not able to figure out as how to add "-a T0002081,T0002083" with in formula to arrive on values similar to C3 column.
just wondering how to flip around values which is separated by used them individually similar to C3, i.e -a "T0002081,T0002083"

Hope , this makes sense here.

Regards
mathetes
Gold Contributor
Nov 14, 2022
I'm so sorry. I'm really not able to understand what you're saying. If anything, it's getting more confusing to me each time.

New PDB name	CTISA_PERM_01_LAC_L1
New Service Name	CTISA_PERM_01_LAC_L1
Cluster Names	T0002081,T0002082,T0002083	This is the test and I want value like like -preferred T0002082 -a T0002081,T0002083