Excel Formula : Convert a text to Number

Got a Excel file from other resource witch Column A is set as text. At the tail and start of number there are some spaces filled in, for example cell A1 is '  4 '.

My challenge is to convert whole column to numbers which can be used to do sum or other math calculation.

With some research, I constructed this formula for a new column :
=VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))

it perfectly resolved this challenge as you can see from following screen shot.

Note: trim will not work with the only space cell. Thanks reply from Hari Krishna.

Checkpoint SmartDashboard Error - No Rule Bases Exist for the Policy Package

Got this issue in the morning. Searched the Checkpoint Support Site and found sk72440

CAUSE

$FWDIR/conf/applications.C* and $FWDIR/conf/CPMILinks* files have become corrupted on Management Server / Multi-Domain CMA.

SOLUTION

To resolve this issue, remove the $FWDIR/conf/applications.C* and $FWDIR/conf/CPMILinksMgr.* files from the Management Server.

On Management Server: 

1. Stop Check Point services:
   
   [Expert@HostName]# cpstop
2. Go to $FWDIR/conf/ directory and remove applications.C* and CPMILinks* files:
   
   [Expert@HostName]# cd $FWDIR/conf/
[Expert@HostName]# mkdir backup_cache
[Expert@HostName]# mv $FWDIR/conf/applications.C* $FWDIR/conf/backup_cache/
[Expert@HostName]# mv $FWDIR/conf/CPMILinks* $FWDIR/conf/backup_cache/
3. Start Check Point services:
   
   [Expert@HostName]# cpstart
4. Connect with SmartDashboard to Management Server.

Notes:

• $FWDIR/conf/applications.C and $FWDIR/conf/CPMILinksMgr.db will be created again automatically.
• The '$FWDIR/conf/backup_cache' directory can be removed after the solution is verified.

Checkpoint Smartview Monitor Shows Firewall Disconnected

It is a interesting error on Checkpoint Smartview Monitor Gateway Status page.

One of cluster members shows disconnected, and another one shows attention. Usually after I refreshed the status, attention will go away, but disconnected status keeps no change in this case.

Followed KB32920, this issue fixed.

SYMPTOMS

In SmartView Monitor, several properties in the 'Gateway View' (such as Security Policy, Security Policy Status, or Firewall Status) display incorrect information about the Security Management server or the Security Gateway.

CAUSE

Some files on the Security Management server are corrupted.

SOLUTION

To resolve the problem, remove the corrupted files from the Security Management server. To do so, perform: Close all GUI clients. Run cpstop. Back up the following files: $FWDIR/conf/applications.C  $FWDIR/conf/applications.C.backup  $FWDIR/conf/CPMILinksMgr.db  $FWDIR/conf/CPMILinksMgr.db.private  Note: Backups are not necessary for files that do not exist. Remove the following files: $FWDIR/conf/applications.C  $FWDIR/conf/applications.C.backup  $FWDIR/conf/CPMILinksMgr.db  $FWDIR/conf/CPMILinksMgr.db.private  Run cpstart. Open SmartDashboard. Install the Security policy. After completing this procedure, the SmartView Monitor displays correct information regarding the Security Management server or the Security Gateway.

------------------------------

[Expert@CP-Management]# cp $FWDIR/conf/applications.C  .
[Expert@CP-Management]# cp $FWDIR/conf/applications.C.backup .
[Expert@CP-Management]# cp $FWDIR/conf/CPMILinksMgr.db  .
[Expert@CP-Management]# cp $FWDIR/conf/CPMILinksMgr.db.private .
[Expert@CP-Management]# ls
CPMILinksMgr.db  CPMILinksMgr.db.private  applications.C  applications.C.backup
[Expert@CP-Management]# rm $FWDIR/conf/applications.C
[Expert@CP-Management]# rm $FWDIR/conf/applications.C.backup 
[Expert@CP-Management]# rm $FWDIR/conf/CPMILinksMgr.db 
[Expert@CP-Management]# rm $FWDIR/conf/CPMILinksMgr.db.private 
[Expert@CP-Management]# ls
CPMILinksMgr.db  CPMILinksMgr.db.private  applications.C  applications.C.backup
[Expert@CP-Management]# cpstart
cpstart: Power-Up self tests passed successfully

cpstart: Starting product - SVN Foundation

SVN Foundation: Starting cpWatchDog
SVN Foundation: Starting cpd
SVN Foundation: cpsnmpd already running
SVN Foundation: Starting PostgreSQL Database
Multiportal daemon: starting mpdaemon
SVN Foundation started

cpstart: Starting product - VPN-1

 Local host is not a FireWall-1 module
FireWall-1: Starting fwd
FireWall-1: Starting fwm (SmartCenter Server)

FireWall-1: This is a SmartCenter server. No security policy will be loaded
FireWall-1 started

cpstart: Starting product - SmartView Monitor

SmartView Monitor: Not active

cpstart: Starting product - Eventia Suite

evstart: dbsync started
Starting SmartReporter...
Starting SmartReporter Server.
Done.
evstart: Starting product - SmartEvent Server
evstart: Starting product - SmartEvent Correlation Unit
Check Point SmartEvent Server started
Check Point SmartEvent Correlation Unit started

cpstart: Starting product - Edge Embedded Connector

cpwd_admin: 
Process VPN-1 Embedded Connector started successfully (pid=11771) 

cpstart: Starting product - Management Portal

Management Portal: Starting CPWMD
CPWMD Started
Management Portal: Starting CPHTTPD
CPHTTPD started

cpstart: Starting product - SmartLog

cpwd_admin: 
Process SMARTLOG_SERVER started successfully (pid=11791) 

cpstart: Starting product - Mobile Access

Mobile Access service is disabled.
If you wish to start Mobile Access, please enable the Mobile Access blade in the SmartDashboard and configure the Mobile Access policy.

cpstart: Starting product - Advanced Routing

Advanced Routing is not enabled. Please use 'cpconfig' to enable it.
----------------------------------

Note: You may need to repeat this process for a couple of times to let management server generate those files correctly.