Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retrieve whole row of data | Excel Worksheet Functions | |||
How to retrieve the number? | Excel Worksheet Functions | |||
How to retrieve the value from formula? | Excel Worksheet Functions | |||
Search and retrieve | Excel Discussion (Misc queries) | |||
Retrieve data | Excel Discussion (Misc queries) |