Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

How do I do this in excel ?

  • 21-04-2015 1:06pm
    #1
    Registered Users, Registered Users 2 Posts: 6,088 ✭✭✭


    I have text files of businesses which I'm attempting to import into excel. I need them divided into several columns, E.G.

    I have this:
    9. Hana Restaurant
    22815 Hawthorne BlvdTorrance, CA 90505
    (310) 373-7560
    Family Style Restaurants


    10. Sekisui Torrance
    1231 Cabrillo AveTorrance, CA 90501
    (310) 781-9200
    Family Style Restaurants

    I need this:
    09. | Hana Restaurant | 22815 | Hawthorne Blvd | Torrance | CA | 90505 | (310) 373-7560 | Family Style Restaurants
    10. | Sekisui Torrance | 1231 | Cabrillo Ave | Torrance | CA | 90501 | (310) 781-9200 | Family Style Restaurants


    The "|" indicates a cell.

    I know I can manually edit the text files & put a comma after each one I need a new cell at, but I've hundreds of them to do.

    Thanks in advance for any help you can give


Comments

  • Registered Users, Registered Users 2 Posts: 5,578 ✭✭✭Slutmonkey57b


    OU812 wrote: »
    I have text files of businesses which I'm attempting to import into excel. I need them divided into several columns, E.G.

    I have this:
    9. Hana Restaurant
    22815 Hawthorne BlvdTorrance, CA 90505
    (310) 373-7560
    Family Style Restaurants


    10. Sekisui Torrance
    1231 Cabrillo AveTorrance, CA 90501
    (310) 781-9200
    Family Style Restaurants

    I need this:
    09. | Hana Restaurant | 22815 | Hawthorne Blvd | Torrance | CA | 90505 | (310) 373-7560 | Family Style Restaurants
    10. | Sekisui Torrance | 1231 | Cabrillo Ave | Torrance | CA | 90501 | (310) 781-9200 | Family Style Restaurants


    The "|" indicates a cell.

    I know I can manually edit the text files & put a comma after each one I need a new cell at, but I've hundreds of them to do.

    Thanks in advance for any help you can give

    That's a nightmare.
    Assuming that your information is in a text file, and your formatting above is accurate, your options are:
    1 use Visual Basic code (usually called VBA) to "read in" the text file, and tell it how to build up an entry for each restaurant. If you don't know code, you'll probably want to avoid this option. Your data isn't well structured and there are a lot of tests and exceptions to deal with line-by-line.
    2. Use the "text to columns" feature to split the sentences into columns, then manually rebuild them. The function will just split the sentence at every space. You'll then use the concatenate function (or a2&a3&...) to combine entries where you need to.


  • Registered Users, Registered Users 2 Posts: 128 ✭✭ancapallliath


    OU812 wrote: »
    I have text files of businesses which I'm attempting to import into excel. I need them divided into several columns, E.G.

    I have this:
    9. Hana Restaurant
    22815 Hawthorne BlvdTorrance, CA 90505
    (310) 373-7560
    Family Style Restaurants


    10. Sekisui Torrance
    1231 Cabrillo AveTorrance, CA 90501
    (310) 781-9200
    Family Style Restaurants

    I need this:
    09. | Hana Restaurant | 22815 | Hawthorne Blvd | Torrance | CA | 90505 | (310) 373-7560 | Family Style Restaurants
    10. | Sekisui Torrance | 1231 | Cabrillo Ave | Torrance | CA | 90501 | (310) 781-9200 | Family Style Restaurants


    The "|" indicates a cell.

    I know I can manually edit the text files & put a comma after each one I need a new cell at, but I've hundreds of them to do.

    Thanks in advance for any help you can give


    2 spreadsheets...

    1st spreadsheet, copy this text into 4 rows
    9. Hana Restaurant
    22815 Hawthorne BlvdTorrance, CA 90505
    (310) 373-7560
    Family Style Restaurants

    Highlight the text and COPY

    2nd spreadsheet right click cell and Paste Special -> Transpose

    this should paste in 4 different columns on 1 row.

    i am not sure of hundreds but someone might be able to build on the above


  • Registered Users, Registered Users 2 Posts: 326 ✭✭nitros7


    Use VBA's Join function. VBA functions aren't exposed in Excel, so I wrap Join in a user-defined function that exposes its functionality:

    Function JoinXL(arr As Variant, Optional delimiter As String = " ")
    'arr must be a one-dimensional array.
    JoinXL = Join(arr, delimiter)
    End Function

    Example usage: Link

    =JoinXL(TRANSPOSE(A1:A4)," ")

    entered as an array formula (using Ctrl-Shift-Enter).


  • Registered Users, Registered Users 2 Posts: 22,656 ✭✭✭✭Tokyo


    As mentioned above, doing that solely in Excel will be an absolute nightmare. The only straightforward way I can see of doing that would be writing a script to parse your text files into a single delimited file, and importing it into excel. Or there are a few text editors which could take most of the work out of delimiting the file, if you knew how to use them.


  • Registered Users, Registered Users 2 Posts: 5,578 ✭✭✭Slutmonkey57b


    2 spreadsheets...

    1st spreadsheet, copy this text into 4 rows
    9. Hana Restaurant
    22815 Hawthorne BlvdTorrance, CA 90505
    (310) 373-7560
    Family Style Restaurants

    Highlight the text and COPY

    2nd spreadsheet right click cell and Paste Special -> Transpose

    this should paste in 4 different columns on 1 row.

    i am not sure of hundreds but someone might be able to build on the above

    All that will do is take an unstructured lost of data set out in rows, and move it into columns instead.


  • Advertisement
  • Registered Users, Registered Users 2 Posts: 2,697 ✭✭✭Thud


    Concatenate

    assuming the list is in the top left of you spreadsheet, add an * (or any other unique character) between each address

    9. Hana Restaurant
    22815 Hawthorne BlvdTorrance, CA 90505
    (310) 373-7560
    Family Style Restaurants

    *
    10. Sekisui Torrance
    1231 Cabrillo AveTorrance, CA 90501
    (310) 781-9200
    Family Style Restaurants


    in cell b1 past this formula
    =a1
    in cell b2 enter
    =b1&" | "&a2

    then drag the formula in cell b2 a to the end of column B

    in the last cell of column B you will then have your list in format you wanted separated by *'s, use text to columns on that cell to separate them


  • Registered Users, Registered Users 2 Posts: 5,578 ✭✭✭Slutmonkey57b


    Thud wrote: »
    Concatenate

    assuming the list is in the top left of you spreadsheet, add an * (or any other unique character) between each address

    9. Hana Restaurant
    22815 Hawthorne BlvdTorrance, CA 90505
    (310) 373-7560
    Family Style Restaurants

    *
    10. Sekisui Torrance
    1231 Cabrillo AveTorrance, CA 90501
    (310) 781-9200
    Family Style Restaurants


    in cell b1 past this formula
    =a1
    in cell b2 enter
    =b1&" | "&a2

    then drag the formula in cell b2 a to the end of column B

    in the last cell of column B you will then have your list in format you wanted separated by *'s, use text to columns on that cell to separate them

    Good plan. However the number of entries may exceed the number of available columns, depending on the dataset.

    What version of excel are you using, OP?


  • Registered Users, Registered Users 2 Posts: 3,782 ✭✭✭dmc17


    Are all the entries in the exact same format OP?


  • Registered Users, Registered Users 2 Posts: 6,088 ✭✭✭OU812


    Thanks everyone, I'm going to attempt some small samples to see if it works & get an understanding for what I'm doing.
    Thud wrote: »
    Concatenate

    assuming the list is in the top left of you spreadsheet, add an * (or any other unique character) between each address

    9. Hana Restaurant
    22815 Hawthorne BlvdTorrance, CA 90505
    (310) 373-7560
    Family Style Restaurants

    *
    10. Sekisui Torrance
    1231 Cabrillo AveTorrance, CA 90501
    (310) 781-9200
    Family Style Restaurants


    in cell b1 past this formula
    =a1
    in cell b2 enter
    =b1&" | "&a2

    then drag the formula in cell b2 a to the end of column B

    in the last cell of column B you will then have your list in format you wanted separated by *'s, use text to columns on that cell to separate them

    Is that pipe symbol in the formula there because I have it in mine ? I put it in to symbolise the different cells, it's not actually in the real data.
    Good plan. However the number of entries may exceed the number of available columns, depending on the dataset.

    What version of excel are you using, OP?

    Excel 2011 for OSX
    dmc17 wrote: »
    Are all the entries in the exact same format OP?

    Mostly. There are a few that have a slightly different format, no spacing between lines etc, but they're separate.


  • Registered Users, Registered Users 2 Posts: 6,088 ✭✭✭OU812


    Thud wrote: »
    Concatenate

    assuming the list is in the top left of you spreadsheet, add an * (or any other unique character) between each address

    9. Hana Restaurant
    22815 Hawthorne BlvdTorrance, CA 90505
    (310) 373-7560
    Family Style Restaurants

    *
    10. Sekisui Torrance
    1231 Cabrillo AveTorrance, CA 90501
    (310) 781-9200
    Family Style Restaurants


    in cell b1 past this formula
    =a1
    in cell b2 enter
    =b1&" | "&a2

    then drag the formula in cell b2 a to the end of column B

    in the last cell of column B you will then have your list in format you wanted separated by *'s, use text to columns on that cell to separate them

    OK, This works (thank you), but when I go to convert text to column, I just get the following
    =B422&" | "&A423


  • Advertisement
  • Registered Users, Registered Users 2 Posts: 5,578 ✭✭✭Slutmonkey57b


    OU812 wrote: »
    OK, This works (thank you), but when I go to convert text to column, I just get the following
    =B422&" | "&A423

    That's because after doing text-to-columns to split the entries at the *, you then need to do another text-to-columns, splitting at the |.

    However that still doesn't actually solve the problem because you still have to break down what each line means and how each line needs to be broken up. Looking into it further, I think VBA is the only way to do this.

    In the data you've given us, the street address and the city aren't separated by a space, e.g.
    " Hawthorne BlvdTorrance, " Torrance needs to be separated from the street by a space for you to be able to split these apart.


  • Registered Users, Registered Users 2 Posts: 10,910 ✭✭✭✭28064212


    The best way to do this is probably going to be using regular expressions (regex).
    1. Download Notepad++ and install. If you need an OSX equivalent, Sublime Text is similar, never used it myself though. The steps are likely very similar
    2. Open (a copy of!!!) your text file using it
    3. Hit Ctrl+H. This opens up the Find and Replace dialog
    4. Select the option that says "Regular expression" (bottom right)
    5. Paste this into the "Find what" box:
      (\d?\.) (.*)\r\n(\d*) ([^,]*), ([a-zA-Z][a-zA-Z]) (.*)\r\n(.*)\r\n(.*)
      
    6. Paste this into the "Replace with" box:
      \1\t\2\t\3\t\4\t\5\t\6\t\7\t\8
      
    7. Hit Replace All
    These steps will replace this:
    9. Hana Restaurant
    22815 Hawthorne BlvdTorrance, CA 90505
    (310) 373-7560
    Family Style Restaurants
    10. Sekisui Torrance
    1231 Cabrillo AveTorrance, CA 90501
    (310) 781-9200
    Family Style Restaurants
    
    With this:
    9.	Hana Restaurant	22815	Hawthorne BlvdTorrance	CA	90505	(310) 373-7560	Family Style Restaurants
    10.	Sekisui Torrance	1231	Cabrillo AveTorrance	CA	90501	(310) 781-9200	Family Style Restaurants
    
    Open Excel, find the text import wizard (not sure where it is on OSX) and make sure to set the delimiter as "Tab"

    Couple of assumptions with this approach:
    • The second line always starts with a number
    • The state is always two letters, and is always preceded by a comma and a space
    • The third and fourth lines always belong to a cell on their own
    Only issue with this approach is that it can't separate the city name, but that's a problem with your input file. Is there no comma or anything between street and city? Is it always Torrance (or a limited number of cities) and you could do a simple search and replace?

    Boardsie Enhancement Suite - a browser extension to make using Boards on desktop a better experience (includes full-width display, keyboard shortcuts, dark mode, and more). Now available through your browser's extension store.

    Firefox: https://addons.mozilla.org/addon/boardsie-enhancement-suite/

    Chrome/Edge/Opera: https://chromewebstore.google.com/detail/boardsie-enhancement-suit/bbgnmnfagihoohjkofdnofcfmkpdmmce



  • Registered Users, Registered Users 2 Posts: 5,578 ✭✭✭Slutmonkey57b


    ^^ I like this a lot even if I don't understand the syntax at all!


    Here is a working solution using VBA if you want it, however as noted above there appears to a problem with your input data.

    To use this solution, copy the text from your file and paste it into the table on sheet 1. The macro will run through the table and reconstruct the data, but it assumes that there is a space between city and street name, and that there's a space between State code and ZIP code. Neither appear to be the case in what you posted.

    I modified a couple of entries so that you can see what happens if the assumption is correct, vs. what happens if it's not. I've run the macro so you can see the output already.


  • Registered Users, Registered Users 2 Posts: 1,370 ✭✭✭eeepaulo


    Where a4 is the address you could search backwards for the first capital, assumes the city line is a capital and doesnt have two words in it.

    Dont have excel to test, never get these things correct first time, but it should be close.

    sub IsCapitalLetter
    Dim strValue
    Dim intPos As Integer

    strValue= range("a4").value
    For intPos = 1 To Len(strValue)
    Select Case Asc(Mid(strValue, Len(strValue) - intPos +1, 1))
    Case 65 To 90, 97 To 122 *is a capital letter
    range("a5").value = right(strValue, intPos)
    range("a4").value = left(strValue, len(strValue) - intpos)
    exit sub
    Case Else
    End Select
    Next
    End sub


  • Registered Users, Registered Users 2 Posts: 6,088 ✭✭✭OU812


    Thanks guys, I'll take a look at this later on this afternoon/evening


Advertisement