Add a Letter to a String based on Condition

[madhuchelliah]

Hello all, i have an alphabet in cell C1, I have data(string) from C2 and column range is dynamic. Condition is if the word in the string has 4 characters, then the alphabet in C1 has to be added as prefix and if the word in a string has 8 characters, then the alphabet in C1 has to be added as first and fifth character of the word. We can ignore words which dont have 4 or 8 characters.

Example: Assume that i have alpha D in cell C1. Data in C2 is A308 E064 E083E086 E141E152 F041F042. Expected output is DA308 DE064 DE083DE086 DE141DE152 DF041DF042

Thank you.

 

[rlv01]

Perhaps something like this.

Book2
	C
1	D
2	A308 E064 E083E086 E141E152 F041F042
3	DA308 DE064 DE083DE086 DE141DE152 DF041DF042
Sheet1

Cell Formulas
Range	Formula
C3	C3	=ModString(C1,C2)

Function ModString(AlphaBet As String, DataStr As String) As String
    Dim NS As String, S As String
    Dim SA As Variant
    Dim I As Long

    DataStr = Application.Trim(DataStr)
    SA = Split(DataStr, " ")
    For I = 0 To UBound(SA)
        S = SA(I)
        Select Case Len(S)
            Case 4
                NS = NS & AlphaBet & S & " "
            Case 8
                NS = NS & AlphaBet & Left(S, 4) & AlphaBet & Right(S, 4) & " "
            Case Else
                NS = NS & AlphaBet & " "
        End Select
    Next I
    ModString = Trim(NS)
End Function

 

[madhuchelliah]

Perhaps something like this.

Book2
	C
1	D
2	A308 E064 E083E086 E141E152 F041F042
3	DA308 DE064 DE083DE086 DE141DE152 DF041DF042
Sheet1

Cell Formulas
Range	Formula
C3	C3	=ModString(C1,C2)

Function ModString(AlphaBet As String, DataStr As String) As String
    Dim NS As String, S As String
    Dim SA As Variant
    Dim I As Long

    DataStr = Application.Trim(DataStr)
    SA = Split(DataStr, " ")
    For I = 0 To UBound(SA)
        S = SA(I)
        Select Case Len(S)
            Case 4
                NS = NS & AlphaBet & S & " "
            Case 8
                NS = NS & AlphaBet & Left(S, 4) & AlphaBet & Right(S, 4) & " "
            Case Else
                NS = NS & AlphaBet & " "
        End Select
    Next I
    ModString = Trim(NS)
End Function

Hey, in the data we may have words with different number of characters also. The funtion is removing the words other than words which has 4 or 8 characters and replacing with just the alphabet. VBA has to look for only 4 and 8 character words and apply the changes. It has to ignore rest of the words in the cell. It would be better if the solution in normal VBA format rather than a function. Thanks for your efforts.

 

[rlv01]

Hey, in the data we may have words with different number of characters also...It has to ignore rest of the words in the cell

That would have been a good thing to mention in your original post and include in your example.

Try this.

Function ModString(AlphaBet As String, DataStr As String) As String
    Dim NS As String, S As String
    Dim SA As Variant
    Dim I As Long

    DataStr = Application.Trim(DataStr)
    SA = Split(DataStr, " ")
    For I = 0 To UBound(SA)
        S = SA(I)
        Select Case Len(S)
            Case 4
                NS = NS & AlphaBet & S & " "
            Case 8
                NS = NS & AlphaBet & Left(S, 4) & AlphaBet & Right(S, 4) & " "
            Case Else
                NS = NS & S & " "
        End Select
    Next I
    ModString = Trim(NS)
End Function

 

[madhuchelliah]

That would have been a good thing to mention in your original post and include in your example.

Try this.

Function ModString(AlphaBet As String, DataStr As String) As String
    Dim NS As String, S As String
    Dim SA As Variant
    Dim I As Long

    DataStr = Application.Trim(DataStr)
    SA = Split(DataStr, " ")
    For I = 0 To UBound(SA)
        S = SA(I)
        Select Case Len(S)
            Case 4
                NS = NS & AlphaBet & S & " "
            Case 8
                NS = NS & AlphaBet & Left(S, 4) & AlphaBet & Right(S, 4) & " "
            Case Else
                NS = NS & S & " "
        End Select
    Next I
    ModString = Trim(NS)
End Function

Sorry for the trouble. It is now working as expected. Let me see if someone can provide the solution in normal VBA. Thanks again