Replace part of a text string with the value of another cell

[Pablo78]

Hi all, I've searched the web for an answer so I'm hoping this can be done.

Basically I am creating a BOM (Bill Of Materials). I am ordering cable in pre manufactured lengths. The cables basically has the same part number A0090CEGAC0BUXXXM the XXX represents the cable length.

I also have to add the cable length in a separate cell, so rather than enter the length twice can I use a formula to replace the XXX with the value of the adjoining cell?


[TABLE="width: 500"]
<tbody>[TR]
[TD]MFR PART #[/TD]
[TD]ORDER QUANTITY[/TD]
[TD]UNIT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody></tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A0090CEGAC0BUXXXM[/TD]
[TD]500[/TD]
[TD]MTS[/TD]
[/TR]
</tbody>[/TABLE]

So for instance the value entered in B3 would replace the XXX in the text string.

Is this possible? I've looked at various instructions on the REPLACE & SUBSTITUTE functions but none seem to suit my needs.

Thanks in advance for any help.

 

[oldbrewer]

the cell could be "A0090CEGACOBU"&b3&"M"

 

[oldbrewer]

if part number always same length a macro would do it easily, if varying length, a macro would do it less easily, are you allowed macros ?

 

[Pablo78]

the cell could be "A0090CEGACOBU"&b3&"M"

Thanks for the reply, what would the formula look like? ie: Substitute or Replace?

 

[Pablo78]

if part number always same length a macro would do it easily, if varying length, a macro would do it less easily, are you allowed macros ?

I've never used Macros before to be honest, can't see there being an issue with using them, however the cable length will change with each cable. I have around 800 cables to order and three instances on each row of xxx in various descriptions, hence I don't really want to enter the various lengths 4 times on each row if I can avoid it.

 

[Pablo78]

I've actually just worked it out.

If I put A0090CEGAC0BUXXXM in Cell A1500 hidden out of the way.

=SUBSTITUTE(A1500, "XXX", B3) it works perfectly. Thanks for your help.

 

[Pablo78]

Ok, so maybe I got a bit too excited there. It kind of works, but only in one cell per row.

As you can see the formula is working in Cell K8, but not M8 or N8? Does anyone have any suggestions on how to make it work in all three cells?