• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replace certain text in a string of text

J

Jhbstudent

New Member
Hi All


What formula would I need to use if I want to replace certain text in a text string with data in other cells?

Example:

Text string

1,"01/03/2011","G","3200000","PMT03001","Bank charges",9.5,0,0," "," ","8400000",1,1,0,0,0,9.5


Need to replace with

20120303 ADMINISTRATION FEE -11


I need to replace 01/03/2011 in text string with 2012/03/03 (in original format)

I need to replace Bank charges with ADMINISTRATION FEE

I need to replace 9.5 and 9.5 with 11 and 11


Many thanks
 
JHBStudent


If you are doing one cell do it manually


If you are doing many cells, Select all cells, use Ctrl H 3 times


You could use 3 Formulas next to each other to substitute

If your string is in A2

in B2:
Code: 
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "01/03/2011","2012/3/3"), "Bank Charges", "Administration Fee"), 9.5,11)


ps: Of course you'll refer to Chandoo.org in your Bibliography if you are a student...
 
Dear Sir


Many thanks for the quick reply. It works perfectly.


Just another question. How do I instead of typing in the "2012/3/3"), "Administration Fee"),11 tell it to fetch the data from another cell on the page. Say for instance 2012/3/3 is in cell C5, Administration Fee is in D5 and 11 is in E5?


So in other words: I need to substitute 01/03/2011 with the data in C5 and Bank Charges with the data in D5 and so on?


Many thanks
 
Put C5 instead of "01/03/2011"

eg: B2: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, C5,"2012/3/3"), D5, "Administration Fee"), 9.5,11)

etc


Substitute uses the format

=Substitute(Text, Text From, Text To)

it will substitute the Text To into all occurrences of Text From in the Text

and in your case there are 3 nested Substitutes
 
Many thanks for your answer.


Is there another formula that can achieve this? Or maybe a combination of formulas?


Thanks
 
You must log in or register to reply here.
Back
Top