意外に便利なExcelマクロ「区切り君」

CSVデータを取り扱う事が多く、ひどく大きいファイルの際は「Access」を利用し取り扱いますが、簡単な作業などの際はやはり「Excel」で編集しています。
同様な作業が多い方に、意外に使えるExcelマクロ「区切り君」を紹介します。とても簡単なコードなので、自分で作っても良いと思います。

ExcelへCSVをコピー&ペーストすると通常(デフォルト状態)は「タブ」区切りとなっているかと思うので、一つの列に行のデータ全てが入ってしまい、「区切り位置の調整」を行うわけですが、ここで、「カンマ」区切りと設定した状態になっていて、すっかり忘れてしまうと、なにか他の作業の際にペーストを行うと、列がズレていたりする事がよくあります。例を挙げると、「¥9,800」などの値をペーストした際に、「¥9」と「800」に値が分かれセルに代入されてしまいます。

気が付かないで作業を続けていて大きな時間のロスとなる事も少なくありません。

そんな時に、「区切り君」を使ってキーボードショートカット一発で、「,」カンマに区切り、設定は変わらず!(実際は「,」カンマ区切りに変更した後、再度「タブ」区切り戻しているだけですが)安心!
私はよく、オートフィルター等でデータを分けて仕分けする際などのデータの一時退避ファイル場所としてもよく利用していますので、デスクトップなどの保存しておくと非常に便利です。お試し下さい。

使い方はとっても簡単!セル「A1」を選択した状態で、「,」(カンマ)区切りのCSVデータをペーストします。データが代入され「A列」が選択された状態になりますので、キーボード「Ctrl」+「Shift」+「Q」を押下すると、きれいに区切られるはずです。

「区切り君」ダウンロード

画像で使ったサンプルデータはなんちゃって個人情報のサービスを利用しました。。

You can leave a response, or trackback from your own site.

62 Responses to “意外に便利なExcelマクロ「区切り君」”

  1. minecraft より:

    Undeniably consider that which you said. Your
    favourite reason seemed to be at the internet the simplest thing
    to take into accout of. I say to you, I definitely get irked whilst folks
    consider concerns that they just do not know about. You managed to hit the nail upon the highest as neatly as defined out the
    whole thing with no need side-effects , other people
    can take a signal. Will probably be again to get more.
    Thanks

  2. minecraft より:

    Thanks for finally talking about > 意外に便利なExcelマクロ「区切り君」 | 浪漫的爆撃機 ~新世界のスノッブ~ < Loved it!

  3. minecraft より:

    Remarkable things here. I am very happy to see your post. Thank you so much and I am taking
    a look forward to contact you. Will you kindly drop me a e-mail?

  4. minecraft より:

    Hello, I enjoy reading through your article post. I like to write a little
    comment to support you.

  5. minecraft より:

    Remarkable issues here. I’m very satisfied to look your article.
    Thank you a lot and I am having a look ahead to touch
    you. Will you please drop me a e-mail?

  6. minecraft より:

    It’s an awesome post in favor of all the web people; they will take advantage
    from it I am sure.

  7. minecraft より:

    Magnificent goods from you, man. I’ve be mindful your stuff previous to
    and you’re simply extremely wonderful. I really like what you have received right here, really like what you’re saying and the best way
    during which you assert it. You make it entertaining and you still care for to keep it sensible.
    I can’t wait to read far more from you. That is really a terrific site.

  8. minecraft より:

    There is definately a great deal to find out about this subject.
    I love all of the points you made.

  9. What’s up, this weekend is nice in favor of me, as this occasion i am reading
    this fantastic informative article here at my house.

  10. Admiring the persistence you put into your website and detailed information you present.

    It’s great to come across a blog every once in a while that
    isn’t the same unwanted rehashed material. Fantastic read!
    I’ve bookmarked your site and I’m including your
    RSS feeds to my Google account.

  11. 物業價值/ 物業種類, 按揭成數上限. 申請人的主要收入 來自香港, 申請人的主要收入 並非來自香港. 首間物業. =$700萬至<$1,000萬, 60

  12. Hey there! I simply want to give you a huge thumbs up
    for the excellent info you have got right here on this post.

    I am coming back to your website for more soon.

  13. Spot on with this write-up, I really think this site needs much more attention. I’ll probably be back again to
    read through more, thanks for the information!

  14. ION MAGNUM より:

    防曬粉 BB粉底霜 亮麗柔滑打底乳液 亮麗柔滑控油打底乳液 有機幹細胞修護CC霜 潤澤防曬底霜 礦物質潤澤慕斯 礦物質奇幻粉餅 礦物質蜜粉 完美礦物粉底 控油定妝蜜粉 高清控油粉餅 控油蜜 保濕滋潤噴霧 有機抗氧爽膚噴霧 有機抗敏保濕

  15. MAJOLICA MAJORCA 戀愛魔鏡 【唇部】HONEY護唇美容液的商品介紹 MAJOLICA MAJORCA 戀愛魔鏡,唇部,HONEY護唇美容液

  16. 山根太寬 より:

    介紹ALBION底粧系列。

  17. Co2激光脫癦 より:

    全「身」趨勢!LSD真空無痛技術,無需使用冷凍啫喱,比傳統激光脫毛更安全,更舒適,更快捷。腿部背部永久脫毛,只需15分鐘!最快激光脫毛科技 配以22x35mm 及 9x9mm 治療機頭,比其他品牌之24mm為大,覆蓋肌膚範圍更廣。 因而更能縮短療程時間及次數。專為亞洲皮膚而設 因應不同膚色設定不同能量,無論膚色較深或較白均適用 最舒適及最有效的激光脫毛體驗 (與755nm及1064nm作比較) 設真空脫毛技術 LUMENIS LightSheer® Desire 激光脫毛儀備有真空脫毛技術,令激光能量更集中聚焦,達至更快及更有效之療程效果。

  18. Do you mind if I quote a couple of your posts as long as I
    provide credit and sources back to your weblog?
    My blog site is in the exact same area of interest as yours
    and my users would genuinely benefit from some of the information you provide here.

    Please let me know if this okay with you. Cheers!

  19. Afar mori 【Mori girl保濕產品系列】保濕修護精華液的商品介紹 Afar mori,Mori girl保濕產品系列,保濕修護精華液

  20. I do not know whether it’s just me or if perhaps everybody else encountering
    problems with your site. It appears as if some of the text in your content are running off the screen. Can somebody else please provide feedback and let
    me know if this is happening to them as well? This might be a issue with my web browser because I’ve had this happen previously.
    Thank you

  21. Heya! I just wanted to ask if you ever have any issues with hackers?
    My last blog (wordpress) was hacked and I ended up losing several weeks
    of hard work due to no data backup. Do you have any methods to stop hackers?

  22. 【保養】超夠力的抗老雙精華! Dr. Wu 超逆齡修復系列讓你不管幾歲都停在最美的時光 @ 潮流、美妝、消費 創造個人化風格的女性社群 PIXstyleMe 【保養】超夠力的抗老雙精華! Dr. Wu 超逆齡修復系列讓你不管幾歲都停在最美的時光

  23. No matter if some one searches for his necessary thing, therefore he/she wishes to be available
    that in detail, therefore that thing is maintained over here.

  24. Coconut Oil より:

    Thanks for finally writing about > 意外に便利なExcelマクロ「区切り君」 | 浪漫的爆撃機 ~新世界のスノッブ~ < Liked it!

  25. Someone essentially help to make severely posts I might state.
    This is the first time I frequented your website page and so far?
    I amazed with the research you made to make this actual publish incredible.

    Magnificent job!

  26. I all the time emailed this webpage post page to all my associates, for the
    reason that if like to read it then my contacts will too.

  27. I really love your blog.. Pleasant colors & theme. Did you create this website yourself?
    Please reply back as I’m wanting to create my own personal site and would love to know where you got this
    from or exactly what the theme is named. Appreciate
    it!

  28. Magnificent goods from you, man. I have understand your stuff prior to and you’re just
    too excellent. I actually like what you’ve received here, certainly like what
    you are saying and the way wherein you assert it.
    You’re making it enjoyable and you still care for to stay it smart.

    I can’t wait to learn far more from you. This is
    actually a terrific web site.

  29. What’s up colleagues, how is the whole thing, and what you want to say on the topic of
    this article, in my view its in fact awesome in support
    of me.

  30. I am truly delighted to glance at this blog posts which
    includes lots of valuable information, thanks for providing such statistics.

  31. Hello, this weekend is pleasant for me, because this occasion i am
    reading this wonderful informative post here at my residence.

  32. Yes! Finally something about quest bars cheap.

  33. quest bars より:

    We’re a group of volunteers and opening a new scheme in our community.
    Your site offered us with helpful information to work on. You’ve performed a formidable activity and our whole
    group might be thankful to you.

  34. Midfielder the star of the show as Wigan increase the pressure on under-fire Blackburn manager Paul Ince with a comprehensive victory at the JJB Stadium. Wigan 3 Blackburn 0: Ince knows the game’s up after Valencia rampage

  35. Pretty! This has been an extremely wonderful article.
    Many thanks for supplying these details.

  36. Rattled Rafa Benitez refused to concede the title to Manchester United on Sunday night, but Liverpool’s bid appears to have collapsed after another dismal display. You’re kidding! Championship hopes still alive reckons Benitez despite seven point gap

  37. We stumbled over here coming from a different web page and thought I may as well check things out.
    I like what I see so i am just following you. Look forward
    to looking over your web page again.

  38. Sling TV より:

    excellent post, very informative. I’m wondering why the opposite experts of this sector do not realize this.

    You should proceed your writing. I am sure, you’ve
    a great readers’ base already!

  39. It’s in reality a great and useful piece of information. I’m happy that you just shared
    this useful info with us. Please keep us informed like this.
    Thank you for sharing.

  40. I really love your site.. Excellent colors & theme.
    Did you make this web site yourself? Please reply back as I’m wanting to create my own personal blog and would like to find out where
    you got this from or just what the theme is called.

    Thank you!

  41. Thank you for the good writeup. It actually was a amusement account it.
    Glance advanced to far brought agreeable from you!
    By the way, how can we keep in touch?

  42. Sling TV より:

    Today, I went to the beach with my kids. I found a sea shell and
    gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put
    the shell to her ear and screamed. There was a hermit crab inside and it pinched
    her ear. She never wants to go back! LoL I know this is completely off
    topic but I had to tell someone!

  43. Sling TV より:

    Today, I went to the beachfront with my children. I found a sea shell and gave it to my 4 year
    old daughter and said “You can hear the ocean if you put this to your ear.” She put the shell to her ear and screamed.
    There was a hermit crab inside and it pinched her ear.
    She never wants to go back! LoL I know this is completely off topic but I
    had to tell someone!

  44. Good way of describing, and nice article to take information about
    my presentation subject matter, which i am going
    to deliver in university.

  45. Sling tv coupons and promo codes for november 2018
    Hello I am so excited I found your site, I really found you by error,
    while I was looking on Digg for something else, Anyhow I am here now and would just like to
    say kudos for a fantastic post and a all round thrilling blog (I also love the theme/design), I don’t have time to look over it all at
    the minute but I have bookmarked it and also added in your RSS feeds, so when I have time I will be
    back to read more, Please do keep up the excellent b. Sling tv coupons and promo codes for
    november 2018

  46. I’m very pleased to find this site. I need to to thank you for ones time due
    to this wonderful read!! I definitely liked every
    little bit of it and i also have you saved to fav to check out new stuff in your site.

  47. Sling tv coupons and promo codes for november 2018
    I all the time used to study paragraph in news papers but now as I am a user of net therefore from now I
    am using net for articles or reviews, thanks to web.
    Sling tv coupons and promo codes for november 2018

  48. I’m gone to tell my little brother, that he should also go to see this weblog on regular basis to take updated from newest reports.

  49. all the time i used to read smaller articles or reviews
    that also clear their motive, and that is also happening with this
    paragraph which I am reading now.

  50. tinyurl.com より:

    Have you ever thought about adding a little bit more than just your
    articles? I mean, what you say is fundamental and everything.
    But think about if you added some great images or video clips to give your posts more, “pop”!
    Your content is excellent but with pics and clips, this
    blog could certainly be one of the greatest in its field.
    Awesome blog!

  51. John Terry is set to miss Chelsea’s crunch Champions League last-16 tie against Paris Saint-Germain. Manager Guus Hiddink had hoped the centre-back would recover from the hamstring injury. Chelsea captain John Terry set to miss crunch Champions League clash against Paris Saint-Germain with hamstring injury

  52. If you are going for most excellent contents like me,
    just visit this website every day since it presents quality contents, thanks

  53. Attractive component to content. I just stumbled upon your website and in accession capital to assert that I get in fact loved
    account your blog posts. Any way I will be subscribing
    to your augment and even I fulfillment you get entry to persistently rapidly.

  54. 토토인증 より:

    Make sure that its location will prevent leakages from
    getting accidently scraped, smooshed, smeared, or touched
    at all. However, it is YOU that has to discover the exact location to put these numbers.
    I began listening to ABBA when I was about 15 possibly even and
    simply leaving school because obviously I had heard a
    few of their songs on the radio and TV but somehow I came
    across an ABBA cassette and played it then played it over
    and over.

  55. Stuart Pearce lambasted England’s Under 21 players after an embarrassing European Championship plummeted to new lows with the defeat to hosts Israel. As Pearce’s job hangs in the balance, beleaguered U21 boss blames everyone but himself for England’s woeful showing

  56. Wow! This blog looks just like my old one! It’s on a totally different subject but it has pretty
    much the same page layout and design. Great choice of
    colors!

  57. She doesn’t really know you yet, you’ve got trapped her into owing you something.

    Heading off and away to college ensures that not only will be the life goals of career and achieving a
    grownup planning to begin, but additionally you are welcomed into the arms of sexual experiences.
    It’s when 2 different people have different end games, or expectations,
    in mind that things will get sticky.

    Here is my homepage :: masturbation sex

Leave a Reply