You seem to have at least 3 spaces between the fields you want to
separate, so you could highlight column A, then CTRL-H (Find &
Replace) to change 3 spaces into some other character (eg pipe | ).
Then you could do Data | Text-to-columns and choose delimited with |
as the delimiter. You might need to use TRIM afterwards to get rid of
the excess spaces.

Hope this helps.

Pete

On Dec 1, 9:20*am, Eric wrote:
Does anyone have any suggestions on how to retrieve the value?
In cell A1, there is a string as shown below,

Example 1
" * * 1 * *CHEUNG KONG * * * * 13.58 * * * *2.11"
I would like to return
1 into cell B1
CHEUNG KONG into cell C1
13.58 into cell D1
2.11 into E1

Example 2
In cell A2, there is a string as shown below,
" * * 3 * *HK & CHINA GAS * * *22.53A * * * 1.51A"
I would like to return
3 into cell B2
HK & CHINA GAS into cell C2
22.53 into cell D2
1.51 into E2

Example 3
" * * 9 * *MANDARIN ENT * * * 131.82A * * * *-"
I would like to return
9 into cell B3
MANDARIN ENT into cell C3
131.82 into cell D3
- into E3

Example 4
" * *27 * *GALAXY ENT * * * * * *- * * * * * -"
I would like to return
27 into cell B4
GALAXY ENT into cell C4
- into cell D4
- into E4

On another example, I find the spacing is different, therefore, I cannot
simply use LEFT and RIGTH function to retrieve the data.

Does anyone have any suggestions on how to solve this problem?
Thanks in advance for any suggestions
Eric