How to retrieve the value?

[Eric]

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

 

[Pete_UK]

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

 

[Eric]

Thank everyone for any suggestions
Could you please give me any suggestions on how to set formula to retrieve
those values?
Thank everyone very much for any suggestions
Eric