How to retrieve the value?
 

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

How to retrieve the value?
 

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

How to retrieve the value?
 

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

"Pete_UK" wrote:

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