Show Underscore from formula bar

[jaydaniel05]

Hello,

I am trying to make naming my files easier. I have a spreadsheet where I have.
FILE NUMBER PROJECT NAME

My boss likes to use the naming convention of:
0001_TPR_PROJECT NAME [TPR being the company name]

I am trying to use =CONCATENATE(TEXT(A1,"0000_TPR_"), B1) but because I can't seem to get the underscores to work. Dashes work, but my boss is pretty adamant on using underscores.

Any chance anyone might have a solution if there is one?

Thank you in advance

 

[jtakw]

Hi,

What's in A1, what's in B1?

 

[jaydaniel05]

Hi,

What's in A1, what's in B1?

Sorry, that would be helpful.....

A1: 0001_TPR_
B1: DINING-KMI0103

When I use that formula I posted, it errors out. But if I change the underscore to dashes in the file number... works perfectly :'(
=CONCATENATE(TEXT(A1,"0000-TPR-"), B1)

 

[jtakw]

Hi again,

I might be misunderstanding something here, if you already have "0001_TPR_" in A1, why do you need the TEXT function?

Book1
	A	B	C	D
1	0001_TPR_	DINING-KMI0103		0001_TPR_DINING-KMI0103
Sheet64

Cell Formulas
Range	Formula
D1	=CONCATENATE(A1,B1)

 

[jaydaniel05]

Hi again,

I might be misunderstanding something here, if you already have "0001_TPR_" in A1, why do you need the TEXT function?

	A	B	C	D
0001_TPR_	DINING-KMI0103	0001_TPR_DINING-KMI0103

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet64​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=CONCATENATE(A1,B1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Ah, that is because i use excel to get my file numbers (click and drag) so my cells are actually regular numbers (1, 2, 3, 4, 5) and I have the cells formatted to show as
0000"_"TPR"_"

I probably made it complicated by doing that....

 

[jtakw]

Ah, that is because i use excel to get my file numbers (click and drag) so my cells are actually regular numbers (1, 2, 3, 4, 5) and I have the cells formatted to show as
0000"_"TPR"_"

I probably made it complicated by doing that....

In that case, you can do this:

Book1
	A	B	C	D
2	0001_TPR_	DINING-KMI0103		0001_TPR_DINING-KMI0103
Sheet64

Cell Formulas
Range	Formula
D2	=CONCATENATE(TEXT(A2,"0000")&"_TPR_",B2)

Value in A2 is just 1, custom formatted 0000"_TPR_"

You have to realize, cell Formatting Only changes the Appearance of the Value of the cell, Not the Value itself.

 

[jaydaniel05]

OH MY GOD. THANK YOU SO MUCH!!!! I have been trying to figure this out for the last two hours! You are a lifesaver!

 

[jtakw]

You're very welcome, glad to help.

 

[TonyUK72]

Deleted.

 

[markmzz]

Hello,

I am trying to make naming my files easier. I have a spreadsheet where I have.
FILE NUMBER PROJECT NAME

My boss likes to use the naming convention of:
0001_TPR_PROJECT NAME [TPR being the company name]

I am trying to use =CONCATENATE(TEXT(A1,"0000_TPR_"), B1) but because I can't seem to get the underscores to work. Dashes work, but my boss is pretty adamant on using underscores.

Any chance anyone might have a solution if there is one?

Thank you in advance

Hi!

You can try this too:

=TEXT(A2,"0000\_TPR\_")&B2

Markmzz