Horizontally align a multirow cell in a table

Just add a multicolumn-environment around your multirow

\documentclass{article}
\usepackage{multirow,multicol}

\begin{document}  

\begin{table}
\begin{tabular}{c l c c c}
\hline 
\multirow{2}{*}{Text}  & \multicolumn{1}{c}{\multirow{2}{*}{ Text to center}} & 1234 & 345 & 543 \\
    & & (Four-digit) & (Three-digit) & (Four-digit) \\
\hline \hline
\multirow{3}{*}{Yes} & Managers, and senior officials & 1 & 2 & 1 \\
 & Professional occupations & 2 & 2 & 2 \\
 & Associate professional occupations & 3 & 3 & 3 \\
\hline
\multirow{3}{*}{No} & Personal service occupations & 5 & 6 & 8 \\   
 & Agricultural, fishery and related & 9 & 9 & 9 \\
 & Health service occupations & 8 & 6 & 6 \\
\hline
\end{tabular} 
\end{table}

\end{document}

Just add an \hfil and \hfill about the item in question.

\documentclass{article}
\usepackage{multirow,multicol}

\begin{document}  

\begin{table}
\begin{tabular}{c l c c c}
\hline 
\multirow{2}{*}{Text}  & \hfil\multirow{2}{*}{Text to center}\hfill & 1234 & 345 & 543 \\
    & & (Four-digit) & (Three-digit) & (Four-digit) \\
\hline \hline
\multirow{3}{*}{Yes} & Managers, and senior officials & 1 & 2 & 1 \\
 & Professional occupations & 2 & 2 & 2 \\
 & Associate professional occupations & 3 & 3 & 3 \\
\hline
\multirow{3}{*}{No} & Personal service occupations & 5 & 6 & 8 \\   
 & Agricultural, fishery and related & 9 & 9 & 9 \\
 & Health service occupations & 8 & 6 & 6 \\
\hline
\end{tabular} 
\end{table}

\end{document}

Use the makecellpackage for that. It allows for line breaks in cells, and a common formatting. By default, the contents of a \makecell command is centred both vertically and horizontally (but this may be changed with an optional argument) whatever the alignment in the column. Additionally, you have one less row.

I took the opportunity to use the rules of booktabs, which adds some vertical padding around them.

\documentclass{article}
\usepackage{multirow,multicol, makecell, booktabs}

\begin{document}

\begin{table}
  \begin{tabular}{c l c c c}
    \toprule
    {Text} & \makecell{Text to center} & \makecell{1234 & & \\ Four-digit} & \makecell{345\\Three-digit }& \makecell{543\\Four-digit }\\
    \cmidrule{1-5}\morecmidrules \cmidrule{1-5}
    \multirow{3}{*}{Yes} & Managers, and senior officials & 1 & 2 & 1 \\
                         & Professional occupations & 2 & 2 & 2 \\
                         & Associate professional occupations & 3 & 3 & 3 \\
    \cmidrule(lr){1-5}
    \multirow{3}{*}{No} & Personal service occupations & 5 & 6 & 8 \\
                         & Agricultural, fishery and related & 9 & 9 & 9 \\
                         & Health service occupations & 8 & 6 & 6 \\
    \bottomrule
  \end{tabular}
\end{table}

\end{document}