Substitute each instance of "B"in a range with value in Col A

need to substitute each instance of "B" in a range B4..g24 with the value in
the Corresponding row, Col A.
--
Al

You could use a formula like

=SUBSTITUTE(UPPER(B4),"B",$A4)

if you want "B" and "b" replaced if you only want "B" replaced

=SUBSTITUTE(B4,"B",$A4)

copy across five columns, then down, once done you can copy and paste
special as values and replace the old table

--
Regards,

Peo Sjoblom

(No private emails please)


"AMK" <[email protected]> wrote in message
news:[email protected]...
> need to substitute each instance of "B" in a range B4..g24 with the value
> in
> the Corresponding row, Col A.
> --
> Al

And maybe one more variation?

=SUBSTITUTE(SUBSTITUTE(B4,"B",UPPER($A4)),"b",LOWER($A4))



Peo Sjoblom wrote:
>
> You could use a formula like
>
> =SUBSTITUTE(UPPER(B4),"B",$A4)
>
> if you want "B" and "b" replaced if you only want "B" replaced
>
> =SUBSTITUTE(B4,"B",$A4)
>
> copy across five columns, then down, once done you can copy and paste
> special as values and replace the old table
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
> "AMK" <[email protected]> wrote in message
> news:[email protected]...
> > need to substitute each instance of "B" in a range B4..g24 with the value
> > in
> > the Corresponding row, Col A.
> > --
> > Al

--

Dave Peterson

Thanks but neither solution appears to work. I need to exchange each instance
of "B" in the Range with the Value in Col A same Row as the "B".

"AMK" wrote:

> need to substitute each instance of "B" in a range B4..g24 with the value in
> the Corresponding row, Col A.
> --
> Al

You'll need the same formula for each cell in that range.

If you put the formula you want in column H4 and drag to M4, then select H4:M4
and drag to row 24, it might just work ok.

AMK wrote:
>
> Thanks but neither solution appears to work. I need to exchange each instance
> of "B" in the Range with the Value in Col A same Row as the "B".
>
> "AMK" wrote:
>
> > need to substitute each instance of "B" in a range B4..g24 with the value in
> > the Corresponding row, Col A.
> > --
> > Al

--

Dave Peterson

Yes, this works just fine. I didn't quite understand it before. Thanks

"Dave Peterson" wrote:

> You'll need the same formula for each cell in that range.
>
> If you put the formula you want in column H4 and drag to M4, then select H4:M4
> and drag to row 24, it might just work ok.
>
> AMK wrote:
> >
> > Thanks but neither solution appears to work. I need to exchange each instance
> > of "B" in the Range with the Value in Col A same Row as the "B".
> >
> > "AMK" wrote:
> >
> > > need to substitute each instance of "B" in a range B4..g24 with the value in
> > > the Corresponding row, Col A.
> > > --
> > > Al
>
> --
>
> Dave Peterson
>