Format Cells: Create custom prefix that will work on both number and text

Shins83

New Member
Joined
Mar 15, 2017
Messages
5
I need to create a custom prefix that will work with cells that contain both number only and number and text data. I've used "000-0"#### but that only works with number only cells. I've also tried "000-0"@ but it only displays the text. There must be a simple solution I can't think of!

Many thanks
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,641
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I need to create a custom prefix that will work with cells that contain both number only and number and text data. I've used "000-0"#### but that only works with number only cells. I've also tried "000-0"@ but it only displays the text. There must be a simple solution I can't think of!
Side note - You do not need multiple # signs unless you want to specify a thousands separator.

This Custom Format will work for positive numbers and text (you did not say what to do for zero or negative values, so I assumed they were not possible)...

"000-0"#;;;"000-0"@
 

Shins83

New Member
Joined
Mar 15, 2017
Messages
5

ADVERTISEMENT

Many thanks Rick, works perfectly.
 

Shins83

New Member
Joined
Mar 15, 2017
Messages
5
I have another question that has issues with the above solution. I've mirrored my main table into another sheet by using Data > Get External Data From Other Sources > From Microsoft Query > Excel Files. The column with serial numbers in, the Query table only shows values with only number values (i.e. 000-01234 is shown, but where 000-01ab2 should be, the cell is blank). Any idea on how I would get the Query table to show all data?

Many thanks
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,110
Messages
5,768,156
Members
425,458
Latest member
Jaspal1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top