Replacin more than 2 letters in a Cell

[mmmarks]

Hi Folk,

I have been facing a query. It quite is who knows it. But Its so difficult to me to find exact Formula for it.

My Question is : say A1 cell has a 20 letters like a sequence. Now I want to replace some of them with different letters.
More clear, Let In A1 cell , among 2o letters of word, I want to replace 5th position with different letter And 10th position with different Letter.

Eg :
In A1 Cell,
SQRUSTUIVWXYXABEFGIJ

I want to replace 5th position by the letter "z" and 10th position by the letter "A"


Can anyone help me out plz?

 

[mmmarks]

yessssssssssssss but i want answer

 

[Peter_SSs]

yessssssssssssss but i want answer

Please be patient. Remember that it is weekend when less helpers tend to be on the forum. Also take note of point 3 of the Posting Guidelines

 

[mmmarks]

Yup

 

[Peter_SSs]

See if this UDF is any use. Use it as shown in the worksheet. The second argument lists the positions and replacements, separated by spaces.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> Swap(<SPAN style="color:#00007F">ByRef</SPAN> s1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> s2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> a<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    a = Split(s2)<br>    <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(a) <SPAN style="color:#00007F">Step</SPAN> 2<br>        s1 = Left(s1, a(i) - 1) & Replace(s1, Mid(s1, a(i), 1), a(i + 1), a(i), 1, 1)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Swap = s1<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

Excel Workbook
	A	B
2	SQRUSTUIVWXYXABEFGIJ	SQRUZTUIVQXFXABEFGIJ
3	BBBBBBBBBBBBBBB	zB7BrBBBBBBBBBB
4	BB	QQQQB
Sheet2

 

[Rick Rothstein]

Function Swap(ByRef s1 As String, ByVal s2 As String) As String

Can I suggest you change s1 from a ByRef argument to a ByVal one. When used as a UDF, Excel will prevent the function from physically changing s1, but if the OP decides to call your function from with his own code later on, your function (as written) would then physically change the contents of a variable he passed in for that first argument.

I also figured you, and others, might find this alternate way of coding your function of some interest...

Function Swap(ByVal SourceText As String, ByVal SwapList As String) As String
    Dim X As Long, Parts() As String
    SwapList = WorksheetFunction.Trim(SwapList)
    Parts = Split(SwapList)
    For X = 0 To UBound(Parts) Step 2
        Mid(SourceText, Parts(X), Len(Parts(X + 1))) = Parts(X + 1)
    Next
    Swap = SourceText
End Function

 

[Rick Rothstein]

I also figured you, and others, might find this alternate way of coding your function of some interest...

Actually, now that I look more carefully, I see my function does not duplicate the functionality for your last example. For this set up...

A4: ABCDEFGHIJ
B4: =Swap(A4,"1 QQQQ")

Your function would output this (substituting many characters for the one)...

QQQQBCDEFGHIJ

whereas mine would output this (substituting one character for one character down the source text instead)...

QQQQEFGHIJ

In that respect, my function would allow this...

=SWAP(A4,"1 X 2 Y 3 Z")

to be shortcutted this way...

=SWAP(A4,"1 XYZ")

However, for single character for single character substitutions, both of our functions would produce the same output.

 

[Peter_SSs]

Can I suggest you change s1 from a ByRef argument to a ByVal one. When used as a UDF, Excel will prevent the function from physically changing s1, but if the OP decides to call your function from with his own code later on, your function (as written) would then physically change the contents of a variable he passed in for that first argument.

I also figured you, and others, might find this alternate way of coding your function of some interest...

Function Swap(ByVal SourceText As String, ByVal SwapList As String) As String
    Dim X As Long, Parts() As String
    SwapList = WorksheetFunction.Trim(SwapList)
    Parts = Split(SwapList)
    For X = 0 To UBound(Parts) Step 2
        Mid(SourceText, Parts(X), Len(Parts(X + 1))) = Parts(X + 1)
    Next
    Swap = SourceText
End Function

Rick

Thanks for your comments and alternate code suggestions - always appreciated.

 

[mmmarks]

Thank x a Lot Mr. Peter.
One more thing is can't we use User Form for this to make easy for users.??

 

[Rick Rothstein]

One more thing is can't we use User Form for this to make easy for users.??

Easier in what way?

What type of interface did you have in mind that you think might be easier for your users to use?

How many of these text strings do you have to process... just one or several?

Where are these text strings normally stored... in a cell range somewhere or a text file?

 

[mmmarks]

Hi Rick

What you have written its more than enough Rick Thanks for that.
I have views and ideas but i don't to make more complex .
It's enough.
Thanks once again!