How do I put one cells values in front of existing numbers?

[Paneross]

Afternoon all,

If you take a look at my spread sheet Column C3-C15 there's coding. Some of the coding has BR in front of it. In H3 there's a branch code, I'd like it when I type in the branch number in H3 it replaces just the BR's in column C but keeps the numbers to the right of where BR. So C6 would ideally now show 2150004216 after I type in the branch code.

Appreciate the help 

[#2]

@Paneross 

If you need to show BR when no branch code is entered in H3 then use this:

=IF(H3,H3&"0004216","BR0004216")

Otherwise just use:

=H3&"0004216"

[#3 · Accepted solution]

@Paneross 

Another way to replace the “BR” values in column C with the branch code entered in cell H3 while keeping the numbers to the right of “BR” is to use a formula that combines the SUBSTITUTE and CONCATENATE functions. Here’s an example formula that you could enter in cell D3 and then copy down to cells D4:D15:

=SUBSTITUTE(C3,"BR",CONCATENATE(H3,""))

This formula uses the SUBSTITUTE function to replace any occurrences of “BR” in cell C3 with the value entered in cell H3. The CONCATENATE function is used to convert the value in cell H3 to a string so that it can be used as the replacement text in the SUBSTITUTE function.

After entering this formula in cell D3 and copying it down to cells D4:D15, column D will contain the updated coding values with the branch code from cell H3 replacing any occurrences of “BR” in column C.

The simple and quick way is what Mr. Riny_van_Eekelen has already suggested. =H3&"0004216"

I hope this helps! 

[#4]

@NikolinoDE @Riny_van_Eekelen thank you both! This answer was exactly what I was looking for.