Show Underscore from formula bar

jaydaniel05

New Member
Joined
Aug 3, 2015
Messages
9
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 :)
 

jaydaniel05

New Member
Joined
Aug 3, 2015
Messages
9
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi again,

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">0001_TPR_</td><td style=";">DINING-KMI0103</td><td style="text-align: right;;"></td><td style=";">0001_TPR_DINING-KMI0103</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet64</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=CONCATENATE(<font color="Blue">A1,B1</font>)</td></tr></tbody></table></td></tr></table><br />
 

jaydaniel05

New Member
Joined
Aug 3, 2015
Messages
9
Hi again,

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

ABCD
10001_TPR_DINING-KMI01030001_TPR_DINING-KMI0103

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet64

Worksheet Formulas
CellFormula
D1=CONCATENATE(A1,B1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">0001_TPR_</td><td style=";">DINING-KMI0103</td><td style="text-align: right;;"></td><td style=";">0001_TPR_DINING-KMI0103</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet64</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=CONCATENATE(<font color="Blue">TEXT(<font color="Red">A2,"0000"</font>)&"_TPR_",B2</font>)</td></tr></tbody></table></td></tr></table><br />

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

New Member
Joined
Aug 3, 2015
Messages
9
OH MY GOD. THANK YOU SO MUCH!!!! I have been trying to figure this out for the last two hours! You are a lifesaver!
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
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
 

Forum statistics

Threads
1,082,373
Messages
5,365,070
Members
400,821
Latest member
kezza123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top