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?

 

[Aladin Akyurek]

Try...

=REPLACE(REPLACE(A1,5,1,IF(LEN(A1)=5,"z","")),20,1,IF(LEN(A1)=20,"A",""))

 

[mmmarks]

Hi Aladin Akyurek,

I have some more queries regarding same question which I asked below,
Your solutions were little bit complex. can make it more easy.
Because I have to REPLACE more character in a particular string
For Eg.
String length may be 120 or 320 etc...not fixed
and replacing position also not fixed for each time it may be @ 12th position or it may be 152th position or more.
My View is :
Can you give a method where I will jut enter position of character and replacing my new character into that particular position.
Hope you understood.

 

[Peter_SSs]

Can you give a method where I will jut enter position of character and replacing my new character into that particular position.

Do you mean like this?
Formula copied down.

Excel Workbook
	A	B	C	D	E
1				Position	5
2	SQRUSTUIVWXYXABEFGIJ	SQRUZTUIVWXYXABEFGIJ		Replacement	Z
3	BBBBBBBBBBBBBBB	BBBBZBBBBBBBBBB
REPLACE

 

[mmmarks]

What You have answered Its works to only for one position .
We can change only one position
My query is to replace multiple position

 

[mmmarks]

Anybody is there to help me out from this Query..

 

[Peter_SSs]

My query is to replace multiple position

Is it just two positions like your original post or might it be 10 or 12 positions etc?

 

[mmmarks]

Changing position is not fixed. It may 5 times or 10 times . Its depends on Client Requirement.
I can say that its not fixed.
I repeat my question once a again.
I have a sequence which length has let say 200.
I have position numbers (where I need to replace ) to change and New letters.

More Clear...
Source : ABCDEFGHIJKLMN
5T,6U,10W so on.

Question is :
5T :represents --->5 is the position
T :represents ----> new letter
@5th position T has to come.

 

[mmmarks]

I have logic but I'm unable to apply that Logic.
My Logic is ..
Create User Form
Then In User Form Give position number where Exactly we need to REPLACE.
Then New character. SO ON...
Once It finished then Click on Finished.
It should be come in a Cell but not in a Original Source Cell .

 

[mohemmedomer]

Hi mmmarks,

You mean to say you have a sequence, suppose it's 'gatttttccc'. Your client says that the 5th position in the sequence which presently is 't' needs to be replaced by 'n', the 7th position ('t' again) with 'p' and the 10th position with 'l'. You also mean to ask that there are many sequences of variable lengths with the specifications to change the original positions in any given sequence with another letter.

Have I correctly understood the question? Kindly let me know.