REDUCE with SUBSTITUTE not quite working

[tigerzen]

Replacing.xlsx
	A	B	C	D	E	F	G	H
1			Should read		Table for changes
2	Course		Course		Old	New
3	Adv Dip Sci		Advanced Diploma Science		Adv	Advanced		Advanced Diploma Science
4	As Deg Business		Associate Degree Business		Dip	Diploma		Associate Degree Business
5	D Bus		Diploma Business		D	Diploma		Diploma Business
6	Cer Maths		Certificate Maths		Cer	Certificate		Certificate Maths
7	D Sci		Diploma Science		As	Associate		Diploma Sci
8					Deg	Degree
9					Bus	Business
10					Sci	Science
Sheet1

Cell Formulas
Range	Formula
H3:H7	H3	=REDUCE(A3,Table1[Old],LAMBDA(a,v,SUBSTITUTE(a,v,OFFSET(v,0,1))))

This mainly works except for the cell in yellow. To get around replacing issues I've entered some of the old values with a trailing space. I was getting Businessiness before using the trailing space. There's probably an easy fix for this but it's beyond me.

 

[DanteAmor]

Delete the formulas from column H and put the following formula in H3:

=REDUCE(A3:A7,Table1[Old],LAMBDA(a,v,SUBSTITUTE(a,v,OFFSET(v,0,1))))

In automatic it will set all the values from H3 to H7.

This is the problem:

 

[tigerzen]

Delete the formulas from column H and put the following formula in H3:

=REDUCE(A3:A7,Table1[Old],LAMBDA(a,v,SUBSTITUTE(a,v,OFFSET(v,0,1))))

In automatic it will set all the values from H3 to H7.

This is the problem:
View attachment 117383

Thanks Dante, I did try the same formula with the array, the problem is still when to include a trailing space and when not to. The Bus entry in E9 contains a trailing space, without it I get Businessiness in H4

 

[hagia_sofia]

Hello, alternatively, you could go with:

=LET(
a,TEXTSPLIT(A3," "),
b,XLOOKUP(a,Table1[Old],Table1[New]),
c,IF(ISNA(b),a,b),
TEXTJOIN(" ",,c))

 

[tigerzen]

Hello, alternatively, you could go with:

=LET(
a,TEXTSPLIT(A3," "),
b,XLOOKUP(a,Table1[Old],Table1[New]),
c,IF(ISNA(b),a,b),
TEXTJOIN(" ",,c))

Hello Hagia_sofia, your formula works! I won't even pretend that I understand it though but thank you for coming up with a formula that works in every situation.

If there is an easier way to achieve the same outcome I'd appreciate it but otherwise I'll try and get my head around this formula.

 

[hagia_sofia]

Many thanks for the feedback. It is not too complicated after all: step a splits original text string into individual elements, step b compares these elements against the lookup table, step c is there to preserve the original elements that are not in the look up table (i.e. those parts that originally are not shortcuts), and the last step joins everything back together. There might be an easier way to do it so maybe somebody will post it...

 

[DanteAmor]

the problem is still when to include a trailing space and when not to

Do not include any extra space in the cells. We solve it in the formula this way:

Put the following formula in H3, in automatic it will set all the values from H3 to H7.

=REDUCE(A3:A7&" ",Tabla1[Old],LAMBDA(a,v,SUBSTITUTE(a&" ",v&" ",OFFSET(v,0,1)&" ")))

ex:

 

[DanteAmor]

I made a small correction in the formula, in the table name:

=REDUCE(A3:A7&" ",Table1[Old],LAMBDA(a,v,SUBSTITUTE(a&" ",v&" ",OFFSET(v,0,1)&" ")))

 

[DanteAmor]

Here is another option to not use the volatile OFFSET function:

=REDUCE(A3:A7&" ",Table1[Old],LAMBDA(a,v,SUBSTITUTE(a&" ",v&" ",VLOOKUP(v,Table1,2,0)&" ")))

 

[tigerzen]

Thanks Dante, that works beautifully! And I really appreciate the Vlookup alternative to Offset.