Displaying Dates with Narrow Non-Breaking Spaces IN EXcel

Written by James McDonald

July 30, 2023

Update: When using CsvView to output CSV UTF-8 you need to tell Excel to open it in UTF-8 format by including the Byte Order Mark with 'bom' => true


        $controller->viewBuilder()
            ->setClassName('CsvView.Csv')
            ->setOptions([ 
                'serialize' => 'items',  
                'header' => $header,
                'extract' => $extract, 
                'bom' => true
            ]);

I have a CakePHP 4 application which uses PHP 8.1 and ICU version 72.1

php -i | grep -i icu
ICU version => 72.1
ICU Data version => 72.1
ICU TZData version => 2022e
ICU Unicode version => 15.0

The dates output by this version of ICU have a nnbsp NARROW NO-BREAK SPACE between the time and the am/pm

When exporting a CSV file using CsvView and opening it in Excel the space between the time and the am/pm is displaying garbled as a number of characters: ` ’

You can simply do a fined and replace of   and move on.

Or you can open as UTF-8 in Excel to display correctly

Click Data => From Text/CSV

The File Origin Should be UTF-8. Click the Load button

The date should now display correctly

To find what character it was

Open the CSV file in VSCode. Copy and paste the garbled character into a PHP script below and run it.

<?php
include '../vendor/autoload.php';

use Cake\I18n\FrozenTime;

$dt = "22/5/23, 6:09?am";

$dtString = FrozenTime::now()->i18nFormat(\IntlDateFormatter::SHORT, 'Australia/Melbourne');

var_dump($dtString);

// need to use mb_str_split NOT str_split
 foreach (mb_str_split($dtString) as $letter) {
    var_dump(['letter' => $letter, mb_ord($letter, 'UTF-8')]);
 };

var_dump(mb_ord('?'));
var_dump(IntlChar::charName('?'));
var_dump('U+' . strtoupper(dechex(IntlChar::ord('?'))));
var_dump(dechex(IntlChar::ord('?')));
var_dump(
    IntlChar::CHAR_CATEGORY_SPACE_SEPARATOR
        ===
        IntlChar::charType(IntlChar::ord('?'))
);

Output

string(18) "30/7/23, 3:31?pm"
array(2) {
  'letter' =>
  string(1) "3"
  [0] =>
  int(51)
}
array(2) {
  'letter' =>
  string(1) "0"
  [0] =>
  int(48)
}
array(2) {
  'letter' =>
  string(1) "/"
  [0] =>
  int(47)
}
array(2) {
  'letter' =>
  string(1) "7"
  [0] =>
  int(55)
}
array(2) {
  'letter' =>
  string(1) "/"
  [0] =>
  int(47)
}
array(2) {
  'letter' =>
  string(1) "2"
  [0] =>
  int(50)
}
array(2) {
  'letter' =>
  string(1) "3"
  [0] =>
  int(51)
}
array(2) {
  'letter' =>
  string(1) ","
  [0] =>
  int(44)
}
array(2) {
  'letter' =>
  string(1) " "
  [0] =>
  int(32)
}
array(2) {
  'letter' =>
  string(1) "3"
  [0] =>
  int(51)
}
array(2) {
  'letter' =>
  string(1) ":"
  [0] =>
  int(58)
}
array(2) {
  'letter' =>
  string(1) "3"
  [0] =>
  int(51)
}
array(2) {
  'letter' =>
  string(1) "1"
  [0] =>
  int(49)
}
array(2) {
  'letter' =>
  string(3) "?"
  [0] =>
  int(8239)
}
array(2) {
  'letter' =>
  string(1) "p"
  [0] =>
  int(112)
}
array(2) {
  'letter' =>
  string(1) "m"
  [0] =>
  int(109)
}
int(8239)
string(21) "NARROW NO-BREAK SPACE"
string(6) "U+202F"
string(4) "202f"
bool(true)

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

List local disks

GET-CimInstance -query "SELECT * from Win32_DiskDrive" DeviceID Caption Partitions Size Model -------- -------...

Create ISO using Powershell

Usage New-IsoFile -NewIsoFilePath C:\tmp\Hyper-V.iso-sources -ImageName Hyper-V -SourceFilePath 'C:\tmp\Hyper-V'...