substitute() multiple text

[lezawang]

Hi
I want to change "street" to "st" and "road" to "rd". I wrote substitute() to change "street" to "st". I also want to change the "road" to "rd". My question, do I need to create another substitute() to change "road" to "rd"?
Can I change both of them using one function instead of doing them twice. Thank you very much.

Book1
	A	B
1	address	new address
2	100 main street	100 main st
3	20 east road
4	88 west blv
5	101 main street
6	21 east road
7	89 west blv
8	102 main street
9	22 east road
10	90 west blv
11	103 main street
12	23 east road
13	91 west blv
14	104 main street
15	24 east road
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUBSTITUTE(A2,"street","st")

 

[kweaver]

Try:

=SUBSTITUTE(SUBSTITUTE(A2,"street","st."),"road","rd.")

 

[jtakw]

Hi,

Yes, you'll need a SUBSTITUTE function for each word you want changed, it can not do arrays.
Just in case you may have street names like A16:A17 in my sample, add a space to the substitute fore & aft:

Book3.xlsx
	A	B
1	address	new address
2	100 main street	100 main st
3	20 east road	20 east rd
4	88 west blv	88 west blv
5	101 main street	101 main st
6	21 east road	21 east rd
7	89 west blv	89 west blv
8	102 main street	102 main st
9	22 east road	22 east rd
10	90 west blv	90 west blv
11	103 main street	103 main st
12	23 east road	23 east rd
13	91 west blv	91 west blv
14	104 main street	104 main st
15	24 east road	24 east rd
16	999 broadway road	999 broadway rd
17	111 roadward road	111 roadward rd
Sheet771

Cell Formulas
Range	Formula
B2:B17	B2	=SUBSTITUTE(SUBSTITUTE(A2&" "," street "," st")," road "," rd")

 

[Sufiyan97]

Hi,

Yes, you'll need a SUBSTITUTE function for each word you want changed, it can not do arrays.
Just in case you may have street names like A16:A17 in my sample, add a space to the substitute fore & aft:

Book3.xlsx
	A	B
1	address	new address
2	100 main street	100 main st
3	20 east road	20 east rd
4	88 west blv	88 west blv
5	101 main street	101 main st
6	21 east road	21 east rd
7	89 west blv	89 west blv
8	102 main street	102 main st
9	22 east road	22 east rd
10	90 west blv	90 west blv
11	103 main street	103 main st
12	23 east road	23 east rd
13	91 west blv	91 west blv
14	104 main street	104 main st
15	24 east road	24 east rd
16	999 broadway road	999 broadway rd
17	111 roadward road	111 roadward rd
Sheet771

Cell Formulas
Range	Formula
B2:B17	B2	=SUBSTITUTE(SUBSTITUTE(A2&" "," street "," st")," road "," rd")

Hello jtakw can you please explain what is reason behind taking A2&" " instead of only A2

 

[jtakw]

A2&" " adds a space at the end of A2 like this (without quotes) "100 main street "
Now the SUBSTITUTE is replacing " street " (with space in front and after) with just " st"
This ensures we're substituting the stand alone word of " street " or " road " and it's not actually a part of a larger word like in my sample A16 & A17.

Without this step, A16:A17 would have resulted in:

999 brdway rd
111 rdward rd

 

[Sufiyan97]

A2&" " adds a space at the end of A2 like this (without quotes) "100 main street "
Now the SUBSTITUTE is replacing " street " (with space in front and after) with just " st"
This ensures we're substituting the stand alone word of " street " or " road " and it's not actually a part of a larger word like in my sample A16 & A17.

Without this step, A16:A17 would have resulted in:

999 brdway rd
111 rdward rd

Thank you very much jtakw
now I understand your formula.

 

[jtakw]

You're welcome, thanks for the feedback.

 

[kweaver]

jtakw makes a good point.

 

[jasonb75]

you'll need a SUBSTITUTE function for each word you want changed, it can not do arrays.

That's not strictly true, although with the complexity of the array method it would need more than just 2 or 3 different words to substitute in order to justify using it.

The formula in column B is best guess for older versions I think it may also need to use T(IF({1},.. for some additional coercion but don't have a suitable version of excel installed for testing it.

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	address	regular excel (may need CSE confirmation)	office 365 dynamic functions		Replace	With
2	100 main street	100 main st	100 main st		street	st
3	20 east road	20 east rd	20 east rd		road	rd
4	88 west boulevard	88 west blv	88 west blv		boulevard	blv
5	101 main street	101 main st	101 main st
6	21 east road	21 east rd	21 east rd
7	89 west boulevard	89 west blv	89 west blv
8	102 main street	102 main st	102 main st
9	22 east road	22 east rd	22 east rd
10	90 west boulevard	90 west blv	90 west blv
11	103 main street	103 main st	103 main st
12	23 east road	23 east rd	23 east rd
13	91 west boulevard	91 west blv	91 west blv
14	104 main street	104 main st	104 main st
15	24 east road	24 east rd	24 east rd
16	999 broadway road	999 broadway rd	999 broadway rd
17	111 roadward road	111 roadward rd	111 roadward rd
Sheet7

Cell Formulas
Range	Formula
B2:B17	B2	=IFERROR(LOOKUP(2,1/SEARCH(" "&$E$2:$E$4&" ",A2&" "),SUBSTITUTE(A2&" "," "&$E$2:$E$4&" "," "&$F$2:$F$4)),A2)
C2:C17	C2	=LET(a,SUBSTITUTE(A2&" "," "&$E$2:$E$4&" "," "&$F$2:$F$4),FILTER(a,a<>A2&" ",A2))

 

[jtakw]

Ahh, yes, wrapping it inside LOOKUP to create the array, I use that method all the time, just didn't think of using it for SUBSTITUTE.
FYI, didn't need CSE for 2016, don't know about other older versions.

Can't speak for all the new functions after Excel 2016, don't have them, so don't know anything about them...