Replacing specific text in string from same column...

[netsecrets00]

Dear All,/

I want to replace specific text with same text in a column. for example in column A having text STM1-1-3-4-1-3-6-3,
STM4-1-2-1-1-3-1-2, STM64-1-6-1-35-3-5-2 in row.
I want to replace STM1-1- / STM4-1- /STM64-1- with "S" and rest text will remain same. This values required in column B. So in cloumn B values will be
S3-4-1-3-6-3, S2-1-1-3-1-2, S6-1-35-3-5-2.

I am trying with substitute function but not able to figure out how to use that with IF function.

[alansidman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[netsecrets00]

Pl find attached excel sheet as requested. I have mentioned Before and After on Tab.

[sktneer]

In the After sheet, the cells contain text which is not in the before sheet. Anyways, if your text string is in B2, try this

Please Login or Register  to view this content.

Or this..

Please Login or Register  to view this content.

Is this what you are trying to achieve?

[netsecrets00]

Hi, need to replace STM1-1- /STM4-1- /STM16-1- /STM64-1- with "S" in all the columns. There I am facing problem. for rest conversion of text string I am using "SUBSTITUTE" function.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"-","-J",2),"-","-K",3),"-","",4),"-","",4)

[sktneer]

Try this....

Please Login or Register  to view this content.

So if the string in B2 is STM1-1-3-4-1-3-6-3, the formula will convert this to S3-4-1-3-6-3.
In your substitute formula, instead of using B2 as a reference, use this formula in place of B2 i.e. where ever there is B2 in your Substitute formula replace it with REPLACE(B2,2,FIND("*",SUBSTITUTE(B2,"-","*",2))-1,"").

Does this help?

[netsecrets00]

Thanks a Lot buddy ... Its working ...

[sktneer]

Glad I could help. Thanks for the feedback.
If that takes care of your question, please mark your thread as solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

[samba_ravi]

See the attachement