Splitting Up a Cell with too much information 
Author Message
 Splitting Up a Cell with too much information

Hello all!  I have about 12,000 rows of data that I need
to process and there is one column that is in an unusable
format.  There is too much information in each cell and I
need to split it apart.  The problem, however, is that the
data in inconsistant.  Lets look at two sample cells.  I
believe all the rows are in one of these two formats...

Column A
Title##Company##StreetAddr#CityStateZipcode
StreetAddress##CityStateZipcode

note:  The ## symbols I put are actually hard returns.  
The cells are designed to be used as mailing addresses.

Anyway, I want to break this apart into 4 columns but I
want the data to end up in the right columns.  I would
split it up by return characters starting from the left,
but since there isn't a consistant format, I would end up
with the first column having some titles and some street
addresses.  It needs to be split up starting from the
right.  There is always a CityStateZip and Street address
on the far right, but there isn't always a Title and/or
company on the far left.

I hope I'm making sense.  Basically I need to split apart
the data into four columns,  Title (if exists), Company
(If exists), StreetAddr, CityStateZip but I want to make
sure the StreetAddr, for example, doesn't end up in the
Title column.

I would appreciate your help, as doing this manually for
over 12,000 records would take way too much effort.  
Thanks for your time.



Tue, 07 Jun 2005 19:04:35 GMT  
 Splitting Up a Cell with too much information
Terry,

Select column A, then choose Edit | Replace....   In the "Find What" field,
hold down the Alt key and type 0010 on your number keypad.  In the "Replace
With" field, choose a character that doesn't occur in your data, like $.
That _should_ replace the hard returns with $'s (but may not if the data was
imported from samewhere else).

Then select Data | Text to Columns.... and use the delimited option and the
$ as the delimiter.  You'll then have two to four columns of data in A:D.
In cell E1, use the formula

=IF($D1<>"",A1,"")

in F1, use

=IF($D1<>"",B1,"")

in G1, use

=IF($D1<>"",C1,A1)
and finally, in H1, use
=IF($D1<>"",D1,B1)

Copy those four formulas down to match your data, then select columns E:H
and do a copy pastespecial/values.  Then delete columns A:D and you're done.

HTH,
Bernie


Quote:
> Hello all!  I have about 12,000 rows of data that I need
> to process and there is one column that is in an unusable
> format.  There is too much information in each cell and I
> need to split it apart.  The problem, however, is that the
> data in inconsistant.  Lets look at two sample cells.  I
> believe all the rows are in one of these two formats...

> Column A
> Title##Company##StreetAddr#CityStateZipcode
> StreetAddress##CityStateZipcode

> note:  The ## symbols I put are actually hard returns.
> The cells are designed to be used as mailing addresses.

> Anyway, I want to break this apart into 4 columns but I
> want the data to end up in the right columns.  I would
> split it up by return characters starting from the left,
> but since there isn't a consistant format, I would end up
> with the first column having some titles and some street
> addresses.  It needs to be split up starting from the
> right.  There is always a CityStateZip and Street address
> on the far right, but there isn't always a Title and/or
> company on the far left.

> I hope I'm making sense.  Basically I need to split apart
> the data into four columns,  Title (if exists), Company
> (If exists), StreetAddr, CityStateZip but I want to make
> sure the StreetAddr, for example, doesn't end up in the
> Title column.

> I would appreciate your help, as doing this manually for
> over 12,000 records would take way too much effort.
> Thanks for your time.



Tue, 07 Jun 2005 20:59:01 GMT  
 Splitting Up a Cell with too much information
Try this code, I think it is what you are after

Sub Parse()
    Dim cell As Excel.Range
    Dim x As String
    Dim c, a, z
    delim = Chr(10)

    Range(Selection, Selection.End(xlDown)).Select
    For Each cell In Selection
        z = 0
        c = 0
        x = ""
        For i = 1 To Len(cell)
            If Mid(cell, i, 1) = delim Then z = z + 1
        Next i
        If z = 0 Then Exit Sub   ' end if no delimitors are found
        ReDim y(z + 1)

        For i = 1 To Len(cell)
            If Mid(cell, i, 1) <> delim Then
                x = x & Mid(cell, i, 1)
            End If

            If Mid(cell, i, 1) = delim Then
                y(c) = x
                c = c + 1
                x = ""
            End If
        Next i
        y(z) = x

        If z = 3 Then a = 1
        If z = 2 Then a = 2    'no title
        If z = 1 Then a = 3    ' no title & Company

        For i = 0 To UBound(y)
            cell.Offset(0, i + a) = y(i)
        Next i
    Next cell

End Sub


Quote:
> Hello all!  I have about 12,000 rows of data that I need
> to process and there is one column that is in an unusable
> format.  There is too much information in each cell and I
> need to split it apart.  The problem, however, is that the
> data in inconsistant.  Lets look at two sample cells.  I
> believe all the rows are in one of these two formats...

> Column A
> Title##Company##StreetAddr#CityStateZipcode
> StreetAddress##CityStateZipcode

> note:  The ## symbols I put are actually hard returns.
> The cells are designed to be used as mailing addresses.

> Anyway, I want to break this apart into 4 columns but I
> want the data to end up in the right columns.  I would
> split it up by return characters starting from the left,
> but since there isn't a consistant format, I would end up
> with the first column having some titles and some street
> addresses.  It needs to be split up starting from the
> right.  There is always a CityStateZip and Street address
> on the far right, but there isn't always a Title and/or
> company on the far left.

> I hope I'm making sense.  Basically I need to split apart
> the data into four columns,  Title (if exists), Company
> (If exists), StreetAddr, CityStateZip but I want to make
> sure the StreetAddr, for example, doesn't end up in the
> Title column.

> I would appreciate your help, as doing this manually for
> over 12,000 records would take way too much effort.
> Thanks for your time.



Tue, 07 Jun 2005 21:28:33 GMT  
 Splitting Up a Cell with too much information
That's a great start!!!!  I did find one problem that
resulted from another format which I hadn't mentioned but
I think I can get around that with sort.  So that leaves
me with only one problem.  When I said there were hard
returns in the cell, I was referring to the two little
squares I saw as deliminators.  When I do search and
Replace, it removes one of them, but not the other.
I guess it's not a return like the other.  I tried to copy
and paste the little square but it didn't work.

It looks like this...
StreetAddr  CityStateZip

Can you tell me what other code might work to get rid of
the other square?  Leaving it in messes up the Text-to-
Columns function.  Thanks for all the help.



Tue, 07 Jun 2005 22:10:47 GMT  
 Splitting Up a Cell with too much information
Terry,

Those are Ascii code 157, so use Alt-0157 to find and replace them.

Below is a {*filter*} macro that will show the characters and code for the
string in the activecell.

HTH,
Bernie

Sub ShowAsciiCodes()
Dim i As Integer
Dim myMsg As String
For i = 1 To Len(ActiveCell.Value)
myMsg = myMsg & Mid(ActiveCell.Value, i, 1) & _
    ":" & Asc(Mid(ActiveCell.Value, i, 1)) & ", "
Next i
MsgBox myMsg
End Sub


That's a great start!!!!  I did find one problem that
resulted from another format which I hadn't mentioned but
I think I can get around that with sort.  So that leaves
me with only one problem.  When I said there were hard
returns in the cell, I was referring to the two little
squares I saw as deliminators.  When I do search and
Replace, it removes one of them, but not the other.
I guess it's not a return like the other.  I tried to copy
and paste the little square but it didn't work.

It looks like this...
StreetAddr  CityStateZip

Can you tell me what other code might work to get rid of
the other square?  Leaving it in messes up the Text-to-
Columns function.  Thanks for all the help.



Tue, 07 Jun 2005 23:39:02 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. split information in one cell to two cells

2. Splitting cell information

3. Splitting cell information

4. DESPERATE! Database corruption/lock-ups after split

5. retrieving information in one cell based on the information in another

6. Splitting Data from 1 cell into 2 cell

7. How to split a string from a cell into two other cells

8. Split Contents of a Cell into two Cells....

9. How to split the contents of a cell into seperate cells - Help please

10. How to split a cell into several cells?


 
Powered by phpBB® Forum Software © phpBB Group