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

[Shins83]

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

 

[Rick Rothstein]

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"@

 

[MickG]

??

 

[Rick Rothstein]

??

Who are those two question marks directed at and exactly what are you questioning?

 

[Shins83]

Many thanks Rick, works perfectly.

 

[Shins83]

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

 

[Shins83]

Not to worry, problem sorted.