Using Excel REPLACE and SUBSTITUTE functions - formula examples, comments page 3

Comments page 3. Total comments: 168

sunny says:
2016-06-17 at 9:12 am

Hi,

i want to replace space in my between my text tell me how can i do it
1. Svetlana Cheusheva says:
  2016-06-17 at 2:37 pm
  
  Hello Sunny,
  
  You can use a formula similar to this:
  =SUBSTITUTE(A1, " ", "text")
  
  Where "text" is a text string that you want to replaces spaces with. If you want to remove spaces, i.e. replaces spaces with nothing, use this formula:
  =SUBSTITUTE(A1, " ", "")
Ben George says:
2016-06-15 at 11:11 pm

Hi Team, Really valuable information on this page, but i'm hoping you can help me with a slightly different formula?
I need to remove the "/" symbol from a date in a different cell, for example, in cell A1 it reads 14/03/1998, and in cell A2 I need it to remove the "/" so it reads 14031998. I have tried using SUBSTITUTE, however this gives me the answer 35868.
Any help would be greatly appreciated!
Thanks.
1. Anon says:
  2016-08-26 at 1:50 am
  
  Just use a format or this should work:
  =text(a1,"DDMMYYYY")
Bob says:
2016-05-05 at 7:14 pm

Good information but ran into an issue converting:

FROM: 110000.350001.704898.104
TO: Fund^110000^Organization^350001^Account^Program^104

Used the formula: =(REPLACE(REPLACE(REPLACE(REPLACE(B7,1,0,"Fund^"),7,0,"^Organization^"),14,0,"^Account^"),21,0,"^Program"))

Which Resulted to: Fund^1^Organi^Accoun^Programt^zation^10000.350001.704898.104

Any suggestions?

Thank you.

Bob
1. RK says:
  2017-01-20 at 8:18 am
  
  use this formula ="fund^"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(T3,".","^C^",3),".","^B^",2),".","^A^",1)
  1. Felipe says:
    2019-04-02 at 1:52 pm
    
    Or you can use this the below. As the nested functions resolve the substitution so you can just use 1 (of the first occurrence)
    ="fund^"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(T3,".","^C^",1),".","^B^",1),".","^A^",1)
Custox says:
2016-03-25 at 4:47 pm

Let's say I have a column containing capitalized words. Of those words, there are some (but not all) that I want to be uppercase.

For example,

BEFORE:
Abc Apple
Xyz Qrs Pear Juice
Sweet Corn
Cherry
Mno Grapes

AFTER:
ABC Apple
XYZ QRS Pear Juice
Sweet Corn
Cherry
MNO Grapes

To make this happen, is it possible to have a couple of additional columns labeled “Old” and “New” that would contain these words I want to uppercase?

For example,

Old New
--- ---
Abc ABC
Xyz Qrs XYZ QRS
Mno MNO

And then use a combination of either SUBSTITUTE and VLOOKUP or SUBSTITUTE and INDEX-MATCH to uppercase only those words that I have set aside in those two additional columns?

The solution doesn’t have to solely rest with those aforementioned formulas, just it seems that is where maybe the answer lies.

Thanks!
1. Rich says:
  2018-09-03 at 1:53 pm
  
  There may be an easier way to achieve what you're asking.
  If the old text started in cell A1 for example, you could simply use the formula =UPPER(A1) in the new text column.
  
  If you also still need to use substitute, you could combine substitute and upper.
  
  For example if you needed "abc Apple" to become "ABC APPLES"
  =SUBSTITUTE(UPPER(A1),"APPLE", "APPLES")
Frank says:
2016-03-13 at 1:55 pm

The axample of substitue is incorrect, last two formulas are the same. See below. To substitue all occurences, the instance should be left blank.

=SUBSTITUTE(A2, "1", "2", 1) - Substitutes the first occurrence of "1" with "2".

=SUBSTITUTE(A2, "1", "2", 2) - Substitutes the second occurrence of "1" with "2".

=SUBSTITUTE(A2, "1", "2", 2) - Substitutes all occurrences of "1" with "2".
1. Frank says:
  2016-03-13 at 1:56 pm
  
  it should be like:
  
  =SUBSTITUTE(A2, "1", "2", 1) - Substitutes the first occurrence of "1" with "2".
  
  =SUBSTITUTE(A2, "1", "2", 2) - Substitutes the second occurrence of "1" with "2".
  
  =SUBSTITUTE(A2, "1", "2") - Substitutes all occurrences of "1" with "2".
rcl says:
2016-02-14 at 1:41 pm

Is it possible to use "*" in SUBSTITUTE? I need to delete all texts containing ".com" plus any other text (I have .com .com.ar .com.ru .com.xxy)

I try this but it doesn´t work:

=SUBSTITUTE(A1,".com*","")

Any suggestion? Can I use wildcards with SUBSTITUTE?

Thanks!

Thanks!
1. Irina Pozniakova (Ablebits Team) says:
  2016-02-19 at 11:46 am
  
  Hello,
  
  The SUBSTITUTE function in Excel doesn't support wildcard characters. You can either use a different formula or the standard Find and Replace tool:
  - press Ctrl+H
  - enter ".com*" in the "Find what" field, leave the "Replace with" field empty
  - click Replace All.
  
  If you prefer using a formula, here is one that can do what you need:
  =IFERROR(LEFT(A1,LEN(A1)-FIND(".com",A1)-1),A1)

Comments on: Using REPLACE and SUBSTITUTE functions in Excel - formula examples

Comments page 3. Total comments: 168