Formula to Replace a Partial String Within a Cell

[pschroeder]

I'm attempting to automate a workbook (without macros) using formulae to tidy/enhance data.

I've made a few unsuccessful attempts at using the SUBSTITUTE command to replace a specific string within a cell.

Is there a combination of commands that can be adopted to achieve this. For example:-

Instruction	Existing Cell Content	Required Cell Content
Replace "Rd" with "Road"	69-75 Sandgate Rd	69-75 Sandgate Road
Replace "Rd" with "Road"	260 Earnshaw Rd	260 Earnshaw Road
Replace "Rd" with "Road"	338 Guardhouse Rd	338 Guardhouse Road
Replace "St" with "Street"	18 Tennant St	18 Tennant Street
Replace "St" with "Street"	14 Daniel St	14 Daniel Street
Replace "St" with "Street"	Unit 3, 97 Banksia St	Unit 3, 97 Banksia Street

Thanks

 

[Peter_SSs]

Could you use something like this?

23 11 01.xlsm
	B	C	D	E	F
1	Existing Cell Content	Required Cell Content		Short	Full
2	69-75 Sandgate Rd	69-75 Sandgate Road		Rd	Road
3	260 Earnshaw Rd	260 Earnshaw Road		St	Street
4	338 Guardhouse Rd	338 Guardhouse Road		Ave	Avenue
5	18 Tennant St	18 Tennant Street
6	14 Daniel St	14 Daniel Street
7	25 Rose Ave	25 Rose Avenue
8	18 Strand Rd	18 Strand Road
9	15 Smith Lane	15 Smith Lane
Replace

Cell Formulas
Range	Formula
C2:C9	C2	=BYROW(B2:B9,LAMBDA(rw,LET(end,TEXTAFTER(rw," ",-1),TEXTBEFORE(rw," ",-1)&" "&XLOOKUP(end,E$2:E$4,F$2:F$4,end))))
Dynamic array formulas.