Replace on the Fly

[rcb007]

I hope this is simple. I am trying to make a formula which can replace part of the value on the fly.

The formula will be in Column C and dragged down. =A1&" - "&B1 is what I am using. I need to change MANHOLE to MH each time it runs into that value.

A1="48inch MANHOLE" B1="R1234" C1="48inch MH - R1234"
A2="24inch INLET" B2="R1234" C2="24inch INLET - R1234"
A3="12inch END CAP" B2="-" C3="12inch END CAD - -"

Thanks for any pointers.

 

[StephenCrump]

=SUBSTITUTE(A1,"MANHOLE","MH")&" - "&B1

 

[rcb007]

Sorry for the question but thank you for the help

 

[rcb007]

From what was shown I need to add another If statements to the formula. However I cant seem to find out why it is not working. I need to add a substitute for "END" to become "FLARED".

A1="48inch MANHOLE" B1="R1234" C1="48inch MH - R1234"
A2="24inch INLET" B2="R1234" C2="24inch INLET - R1234"
A3="12inch END CAP" B2="-" C3="12inch END CAD - -"

C1 =IF(A1="*MANHOLE*",(SUBSTITUTE(A1,"MANHOLE","MH")&" - "&B1),IF(A1="*END*",(SUBSTITUTE(A1,"END","FLARED"))),A1&" - "&B1)) ---- Then Drag down formula.

Thank you again.

 

[Fluff]

How about

=SUBSTITUTE(SUBSTITUTE(A1,"MANHOLE","MH"),"END","FLARED")&" - "&B1

 

[rcb007]

Thank you. Apparently I am over thinking this.

 

[Fluff]

Glad we could help & thanks for the feedback.