Insert string before delimiter

[GreyFox8991]

Greetings Excel Community,

I am attempting to insert or duplicate a string value within a naming convention without having to do input manually. Is there a formula or a macro code that might be able to complete this possibly? Via the example below, I am attempting to duplicate the ID value twice so the end result is the format presented in the UpdatedNamingConvention, so the ID value appears twice.

Book4
	A	B	C
1	ID	CurrentNamingConvention	UpdatedNamingConvention
2	999999	POTTER_HARRY_999999.txt	POTTER_HARRY_999999_999999.txt
Sheet1

 

[Joe4]

So, you have what you show in columns A and B to start, and you want what is in column C?

If that is the case, try this formula in cell C2:

=LEFT(B2,LEN(B2)-4) & "_" & A2 & RIGHT(B2,4)

 

[Rick Rothstein]

Maybe this formula in cell C2...

=SUBSTITUTE(B2,A2,A2&"_"&A2)

 

[Scott Huish]

Maybe this formula in cell C2...

=SUBSTITUTE(B2,A2,A2&"_"&A2)

I think that would only work if it was always a duplication.

Perhaps:
=SUBSTITUTE(B2,".","."&A2&".")

 

[Rick Rothstein]

I think that would only work if it was always a duplication.

Perhaps:
=SUBSTITUTE(B2,".","."&A2&".")

The OP said "I am attempting to duplicate the ID value twice so the end result is the format presented in the UpdatedNamingConvention", hence, my formula.

 

[Scott Huish]

But the op also said insert or duplicate, so I guess it just depends on how you interpret that. Otherwise, there is no reason for the value in column A, you could just duplicate the part after the last _

 

[Rick Rothstein]

But the op also said insert or duplicate, so I guess it just depends on how you interpret that. Otherwise, there is no reason for the value in column A, you could just duplicate the part after the last _

I assumed the value was in the first column because it was used to create the value in the second column (the original file name) and that the OP now wanted to modify it to duplicate the number in the name (and put that in the third column). I figured as long as the values were already existing, that I would use them. And yes, I agree, we need a clarification from the OP.

 

[dermie_72]

my first question would be....does Cell A2, go at the END of the string in B2 or the middle of it? Because they're named the same it's hard to differentiate.

 

[Fluff]

Another option is

=REPLACE(B2,FIND(".",B2),0,"_"&A2)