1

I have an MS Excel 2013 spreadsheet in which I have the list of some people attending a conference, their address, phone number etc in different tabs. Now, I've been asked to create a tab delimited list of the attendees for a brochure. Is there a way which can do it without forcing me to retype/copy-paste every single line of the data the data? I only need the name column. I wish to prepare the brochure on MS Publisher 2013. Any help will be appreciated.

5
  • 1
    Public Function get_list(rng As Range) As String: Dim str As String, i As Long: i = 1: While Len(rng(i).Value): str = str & ", " & rng(i).Value: i = i + 1: Wend: get_list = Mid(str, 2): End Function :D Jan 2 '16 at 19:44
  • @DirkReichel Can you please tell me what language this is and how to implement it in the spreadsheet? Jan 3 '16 at 5:26
  • 1
    This is vba... if you go to the vba module by clicking alt+f11 and then insert a module, you can simply copy/paste the code there. Then in your worksheet insert =get_list(A:A) (or whatever range your names are in) and it will output a , seperated list of all cell values in the given range (will stop at the first empty cell)... sorry, but I was in a hurry and just by phone, this way no big answer with an explanation :P Jan 3 '16 at 11:13
  • 1
    Still, you would need to change the , to a vbTab. However, you can't set tabs in a cell, this way it would not work as desired :P Jan 3 '16 at 11:18
  • @DirkReichel Thanks. I look forward to learning VBA. Jan 3 '16 at 11:20
1

Get tab delimited text from a specific column in excel

I've asked to create a tab delimited list of the attendees for a brochure. Is there a way which can do it without forcing me to retype/copy-paste every single line of the data the data? I only need the name column.

Here are the steps to do this manually and quickly with Notepad++ in case you're looking for a manual way to complete this easily without code otherwise; plus you have your question tagged at text as well.

  1. Open your Excel spreadsheet with the column you wish to get the data values from such as the name column.

  2. Click that column to highlight all values and then press Ctrl+C (or right click and select Copy).

  3. Open Notepad++ and a new new blank document and then and then press Ctrl+V (or right click and select Paste) to paste the values of the column you just copied.

  4. From within Notepad++ with the data values pasted press Ctrl+A to select everything you pasted, go up and select Edit select Line Operations, and then select Remove Empty Lines (Containing Blank characters)

  5. From within Notepad++ with the data values still all highlighted go up and select Edit select Blank Operations and then select Trim Leading and Trailing Space

  6. From within Notepad++ with the data values still all highlighted press Ctrl+H go to the Replace tab and put in the values and ensure the options are selected just as shown below. Press the Replace All option and that should tab delim all your data values.

  7. From there you can save the newly formatted values as a new text document or copy and paste in that formatt (tab delimited) to your other applications, etc. as-needed if applicable.

Please note I did test this out and confirmed it worked as expected and then turned right back around and imported it into Excel as tab delimited and everything appears to be tab delimited and imports correctly.


Screen Shot

enter image description here

Source: Notepad++ Regular Expressions

\n

  • The LF control character 0x0A (line feed). This is the regular end of line under Unix systems.

\r

  • The CR control character 0x0D (carriage return). This is part of the DOS/Windows end of line sequence CR-LF, and was the EOL character on Mac 9 and earlier. OSX and later versions use \n.

\t

  • The TAB control character 0x09 (tab, or hard tab, horizontal tab).

Additional Note

There is also the Ctrl+A and Ctrl+J feature of Notepad++ but it'll not add the tab to delimit but rather spaces. I'm not sure if your "name" column is first and last name both or just one name only which you could potentially do "space" delimited if you needed to and that works in your case.

4
  • Yeah. It contains both first and last names. And your process worked. Although for my purpose, I found that adding multiple spaces was better than tabs as I need uniform spacing. I'm helping an NGO and it was a last minute work. Thanks for your help. Also, I notice some code in the comments. Can you please tell me what language is that and how to use it? Jan 3 '16 at 3:31
  • 1
    @SayantanSantra I'm glad that helped resolve your issue for your immediate need. Thanks for the question! The code in the comment above by someone else appears to be VB or VBA perhaps. It's not formatted properly because it's in a comment but VB from what I can tell. Jan 3 '16 at 4:58
  • Yeah. Thanks. I know c++, but not VB or VBA. I look forward to learning it. Jan 3 '16 at 5:25
  • You also "could" transpose it to one row and then save as tab-delimetered csv Jan 3 '16 at 11:20
1

I assume that you have some knowledge with programming. This way the given names should explain everything. (So i will not explain how the code works.)

To show a proper way to do it with VBA (and make it available to other applications) You could use this script:

First open your VBA window via Alt+F11. Then "Insert" and "Module". You can simply copy/paste the code there

Option Explicit
Sub Put_All_Names_to_Clipboard()
  Dim Name_In_Cell As Variant, Name_List As String
  For Each Name_In_Cell In Selection.Value
    If Len(Name_In_Cell) = 0 Then
      Exit For
    ElseIf Len(Name_List) Then
      Name_List = Name_List & vbTab & Name_In_Cell
    Else
      Name_List = Name_In_Cell
    End If
  Next
  Dim Object_For_Clipboard As Object
  Set Object_For_Clipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  Object_For_Clipboard.SetText Name_List
  Object_For_Clipboard.PutInClipboard
  Set Object_For_Clipboard = Nothing
End Sub

It will now put a tab-separated list (text string) of all call values which are selected (it stops at the first empty cell, this way feel free to select a whole column). After the macro is executed, you can simply "paste" the string wherever you want it. However, I suggest to paste it to the right place directly in your publisher-file, which saves 1 step and also keeps your clipboard clean for other tasks.

You can Start the macro directly by having the text cursor somewhere inside the sub and hit F5. You also can assign the macro to a button or add it to the toolbar

As a last hint: You also can crate a macro directly in MS-Publisher and just "get" this string from the excel-file. But that is something you can do later on if you feel better with VBA. :)

3
  • Yeah. I do have some experience. I am an amateur c++ programmer. Your answer explains quite well. But, as the other answer was more user friendly and usable for everyone. That helped me actually get the job done at the hour of need. So, I accepted that instead. Anyway, thank you for this great answer. I shall try to get familiar with VBA in the near future. Jan 3 '16 at 12:50
  • 1
    @SayantanSantra I'm not here to get "points" or anything like that. My mission is to help other ppl to get better. And IF you are able to improve your skills than I got all I was looking for :) Jan 3 '16 at 12:57
  • 1
    That I'll try to make sure. 😅 Jan 3 '16 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.