| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to separate value into different cells?
Does anyone have any suggestions on how to separate the value into different
cells? For example, the text in cell A1 and A2 are shown below 1 CHEUNG KONG 929,000 83,737,500 3,933,462 354,652,820 196 HONGHUA GROUP 175,000 223,950 1,817,000 2,333,084 I would like to retrieve the last 4 values into 929,000 in AA1, 83,737,500 in AB1, 3,933,462 in AC1, 354,652,820 in AD1 175,000 in AA2, 223,950 in AB2, 1,817,000 in AC2, 2,333,084 in AD2 Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to separate value into different cells?
Try the below formula in cell AA1 and copy down/across as required...
=TRIM(MID(SUBSTITUTE(" " & $A1& REPT(" ",6)," ", REPT(CHAR(32),255)),COLUMNS($B$1:D$1)*255,255)) -- Jacob "Eric" wrote: Does anyone have any suggestions on how to separate the value into different cells? For example, the text in cell A1 and A2 are shown below 1 CHEUNG KONG 929,000 83,737,500 3,933,462 354,652,820 196 HONGHUA GROUP 175,000 223,950 1,817,000 2,333,084 I would like to retrieve the last 4 values into 929,000 in AA1, 83,737,500 in AB1, 3,933,462 in AC1, 354,652,820 in AD1 175,000 in AA2, 223,950 in AB2, 1,817,000 in AC2, 2,333,084 in AD2 Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to separate value into different cells?
Thank you very much for suggestions
Those values seem to be separated by spacing with variable space / position, so your suggested code with specific position does not work, do you have any suggestions on how to modify the code to retrieve those values? Thank you very much for any suggestions Eric "Jacob Skaria" wrote: Try the below formula in cell AA1 and copy down/across as required... =TRIM(MID(SUBSTITUTE(" " & $A1& REPT(" ",6)," ", REPT(CHAR(32),255)),COLUMNS($B$1:D$1)*255,255)) -- Jacob "Eric" wrote: Does anyone have any suggestions on how to separate the value into different cells? For example, the text in cell A1 and A2 are shown below 1 CHEUNG KONG 929,000 83,737,500 3,933,462 354,652,820 196 HONGHUA GROUP 175,000 223,950 1,817,000 2,333,084 I would like to retrieve the last 4 values into 929,000 in AA1, 83,737,500 in AB1, 3,933,462 in AC1, 354,652,820 in AD1 175,000 in AA2, 223,950 in AB2, 1,817,000 in AC2, 2,333,084 in AD2 Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to separate value into different cells?
I understand the spacing is variable...and so I have kept that as 4
spaces....(which is the least spacing in your sample data which is between the 1st number and second number) It worked for me when I tried with the sample data you posted.... -- Jacob "Eric" wrote: Thank you very much for suggestions Those values seem to be separated by spacing with variable space / position, so your suggested code with specific position does not work, do you have any suggestions on how to modify the code to retrieve those values? Thank you very much for any suggestions Eric "Jacob Skaria" wrote: Try the below formula in cell AA1 and copy down/across as required... =TRIM(MID(SUBSTITUTE(" " & $A1& REPT(" ",6)," ", REPT(CHAR(32),255)),COLUMNS($B$1:D$1)*255,255)) -- Jacob "Eric" wrote: Does anyone have any suggestions on how to separate the value into different cells? For example, the text in cell A1 and A2 are shown below 1 CHEUNG KONG 929,000 83,737,500 3,933,462 354,652,820 196 HONGHUA GROUP 175,000 223,950 1,817,000 2,333,084 I would like to retrieve the last 4 values into 929,000 in AA1, 83,737,500 in AB1, 3,933,462 in AC1, 354,652,820 in AD1 175,000 in AA2, 223,950 in AB2, 1,817,000 in AC2, 2,333,084 in AD2 Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to separate value into different cells?
The following example does not work
144 CHINA MER HOLD 196,000 4,947,200 6,224,438 157,394,072 Do you have any suggestions? Thank you very much for any suggestions Eric "Jacob Skaria" wrote: I understand the spacing is variable...and so I have kept that as 4 spaces....(which is the least spacing in your sample data which is between the 1st number and second number) It worked for me when I tried with the sample data you posted.... -- Jacob "Eric" wrote: Thank you very much for suggestions Those values seem to be separated by spacing with variable space / position, so your suggested code with specific position does not work, do you have any suggestions on how to modify the code to retrieve those values? Thank you very much for any suggestions Eric "Jacob Skaria" wrote: Try the below formula in cell AA1 and copy down/across as required... =TRIM(MID(SUBSTITUTE(" " & $A1& REPT(" ",6)," ", REPT(CHAR(32),255)),COLUMNS($B$1:D$1)*255,255)) -- Jacob "Eric" wrote: Does anyone have any suggestions on how to separate the value into different cells? For example, the text in cell A1 and A2 are shown below 1 CHEUNG KONG 929,000 83,737,500 3,933,462 354,652,820 196 HONGHUA GROUP 175,000 223,950 1,817,000 2,333,084 I would like to retrieve the last 4 values into 929,000 in AA1, 83,737,500 in AB1, 3,933,462 in AC1, 354,652,820 in AD1 175,000 in AA2, 223,950 in AB2, 1,817,000 in AC2, 2,333,084 in AD2 Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to separate value into different cells?
Could it be possible to remove any text on the left? then retrieve each value
one by one, which are separated by spacing. For example, 144 CHINA MER HOLD 196,000 4,947,200 6,224,438 157,394,072 It will become 196,000 4,947,200 6,224,438 157,394,072 then each number can be separated by spacing. Do you have any suggesitons? Thank you very much for any suggestions Eric "Jacob Skaria" wrote: I understand the spacing is variable...and so I have kept that as 4 spaces....(which is the least spacing in your sample data which is between the 1st number and second number) It worked for me when I tried with the sample data you posted.... -- Jacob "Eric" wrote: Thank you very much for suggestions Those values seem to be separated by spacing with variable space / position, so your suggested code with specific position does not work, do you have any suggestions on how to modify the code to retrieve those values? Thank you very much for any suggestions Eric "Jacob Skaria" wrote: Try the below formula in cell AA1 and copy down/across as required... =TRIM(MID(SUBSTITUTE(" " & $A1& REPT(" ",6)," ", REPT(CHAR(32),255)),COLUMNS($B$1:D$1)*255,255)) -- Jacob "Eric" wrote: Does anyone have any suggestions on how to separate the value into different cells? For example, the text in cell A1 and A2 are shown below 1 CHEUNG KONG 929,000 83,737,500 3,933,462 354,652,820 196 HONGHUA GROUP 175,000 223,950 1,817,000 2,333,084 I would like to retrieve the last 4 values into 929,000 in AA1, 83,737,500 in AB1, 3,933,462 in AC1, 354,652,820 in AD1 175,000 in AA2, 223,950 in AB2, 1,817,000 in AC2, 2,333,084 in AD2 Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Separate Cells 2 | Excel Discussion (Misc queries) | |||
| separate two dates from a text string into two separate cells | Excel Discussion (Misc queries) | |||
| Elapsed time when separate cells contain time and separate date | New Users to Excel | |||
| Separate first and second name in one cell into separate cells. | Excel Discussion (Misc queries) | |||
| Separate first and second name in one cell into separate cells. | Excel Discussion (Misc queries) | |||
Hybrid Mode
