How do I do this in excel ?

OU812

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

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.

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

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).

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.

Slutmonkey57b

ancapallliath wrote: »

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.

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

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?

dmc17

Are all the entries in the exact same format OP?

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.

Slutmonkey57b wrote: »

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.

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

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.

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?

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.

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

OU812

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