標題:
excel splitting problem
發問:
i have a excel file, in each cell there are several set of words, such asabc abc abc abcbcd bcd bcd bcdthe the two rows are in the same CELL, after end of first row, ppl use alt + enter to go to second row of the same CELLand i have thousands of these cells wanna split up.anyways that i can split out... 顯示更多 i have a excel file, in each cell there are several set of words, such as abc abc abc abc bcd bcd bcd bcd the the two rows are in the same CELL, after end of first row, ppl use alt + enter to go to second row of the same CELL and i have thousands of these cells wanna split up. anyways that i can split out those into different cells? 更新: My case is like this 有幾千個cells，每個cells 有幾行字，你想將每一行字 split 番獨立 cells 的話, any experts can help me please? and also............每一行字 is different 更新 2: This is kinda a good way, but actually my file is having more than 200 columns, it takes many times to do it column by column. I've found another way myself, copy whole thing to word, and word's REPLACE function and switch line break to column break, then copy it back to excel and done.
最佳解答:
係excel 度我地叫一張表為一張worksheet， 一張worksheet 橫去到 vv (256 columns)，直有65536 (256 x 256 rows) 我唔明你split 係想點split 如果只係split 番係cells 內的文字，定係要將欄 / 列 split up 所以你個問題係好唔清楚。 假設你有幾千個cells，每個cells 有幾行字，你想將每一行字 split 番獨立 cells 的話，你就有排搞。 但係如果只係每個cells 得2行，想拆開兩行文字的話 我可以提供以下方法： 先將worksheet 複製一張，放在後面。 如果每行文字相同的話，就很容易解決，按 〔ctrl + H〕尋求及取代 將第二行文字複製至〔尋找〕欄，〔取代〕欄留空白，按全部取代 便可得回第一行文字，如是者到第2張 WORKSHEET 去取代第一行 便可得回第二行文字，這個方法可以套用到不同的空白取代方法。
其他解答:
excel splitting problem
發問:
i have a excel file, in each cell there are several set of words, such asabc abc abc abcbcd bcd bcd bcdthe the two rows are in the same CELL, after end of first row, ppl use alt + enter to go to second row of the same CELLand i have thousands of these cells wanna split up.anyways that i can split out... 顯示更多 i have a excel file, in each cell there are several set of words, such as abc abc abc abc bcd bcd bcd bcd the the two rows are in the same CELL, after end of first row, ppl use alt + enter to go to second row of the same CELL and i have thousands of these cells wanna split up. anyways that i can split out those into different cells? 更新: My case is like this 有幾千個cells，每個cells 有幾行字，你想將每一行字 split 番獨立 cells 的話, any experts can help me please? and also............每一行字 is different 更新 2: This is kinda a good way, but actually my file is having more than 200 columns, it takes many times to do it column by column. I've found another way myself, copy whole thing to word, and word's REPLACE function and switch line break to column break, then copy it back to excel and done.
最佳解答:
係excel 度我地叫一張表為一張worksheet， 一張worksheet 橫去到 vv (256 columns)，直有65536 (256 x 256 rows) 我唔明你split 係想點split 如果只係split 番係cells 內的文字，定係要將欄 / 列 split up 所以你個問題係好唔清楚。 假設你有幾千個cells，每個cells 有幾行字，你想將每一行字 split 番獨立 cells 的話，你就有排搞。 但係如果只係每個cells 得2行，想拆開兩行文字的話 我可以提供以下方法： 先將worksheet 複製一張，放在後面。 如果每行文字相同的話，就很容易解決，按 〔ctrl + H〕尋求及取代 將第二行文字複製至〔尋找〕欄，〔取代〕欄留空白，按全部取代 便可得回第一行文字，如是者到第2張 WORKSHEET 去取代第一行 便可得回第二行文字，這個方法可以套用到不同的空白取代方法。
其他解答:
此文章來自奇摩知識+如有不便請留言告知
假設你的Alt + enter 的cells 是 1 row x many columns, 想將獨立cells順序放在each original cell下面。用我剛剛編寫的下列巨集吧! Sub splitX() Set xxx = Selection For Each xx In xxx x = xx.Value r = 0 resplit: n = Len(x) For t = 1 To n If Mid(x, t, 1) = Chr(10) Then r = r + 1 xx.Offset(r, 0) = Mid(x, 1, t - 1) x = Mid(x, t + 1) GoTo resplit End If Next r = r + 1 xx.Offset(r, 0) = x Next End Sub HIGHLIGHT the Alt-enter cells, 然後執行巨集, 唔駛1秒, 完成。 如 Alt-enter cells 是 1 column x many rows 或 many column x many rows, 祇要將上述巨集修改幾個字就攪掂。 如不懂程式及執行巨集, 請依以下方法: 按 Alt + [F11] → 會有一個 module方塊出現 將上述巨集 (即由 Sub ............End Sub)複製, 貼上 module方塊上 將module方塊關閉 Highlight the Alt-enter cells, 按 工具(T) → 巨集(M) → 巨集(M) → 選 SplitX → 按 執行, 完成 如有任何問題, 請補充|||||如果 abc 及 bcd 是用空格分開，問題就簡單了，我想很多人就懂得如何用 Text to Columns 來將它們分開。 所以關鍵是怎樣可以將用 Line Break (即用 Alt+Enter 打出來的 Character) 來做 Delimiter 去分開 abc 同 bcd 。 答案是 Alt+0010 (即按著 Alt 鍵然後在鍵盤右邊的 Num Pad 按 0010) 詳情請看以下示範，用法就如普通 Text to Columns ，不過要在 Delimiter 裏按照上述方法打 Alt+0010 (記得 0010 是按 Num Pad 上的數字不是 QWER 上的) 就可以了，： http://www.geocities.com/emailtobiglin/excel/excel.html 若你想將它們分開到不同 Row 裏，可以先將它們全部分到不同 Column 裏，然後 Copy 及 Paste Special 的 Transport 去做對角轉移，這個應該不難，不用詳述。
文章標籤
全站熱搜
留言列表
