Excel 2003: Text to columns help

[ericb1]

Any help is greatly appreciated. I'm using Excel2003, and I inherited a spreadsheet with a column that has a large amount of fixed witdth data in it, in the format of "company-date".

But the companies vary in name and length, so when I try to use the text to columns wizard, I can't add column breaks because it would cut off some names.

How can I get this data out of this one column and into separate columns, perhaps linked to another column in the spreadsheet?

This was given to me, but it's really database data that I'm trying to get back into a database, so column A is a unique identifying number, then this column, col B, should be broken out into separate rows and columns so that I can then import it correctly into my database.

Any help is greatly appreciated, thanks!

 

[SkipVought]

Hi,

Is the date portion of the string the same length in each row? If so and assuming the length is 8,

Co...
[tt]
=Left(A1,Len(A1)-8)
[/tt]
Date...
[tt]
=Date(Right(A1,8))
[/tt]
Now you may need to do something with the STRING representing the DATE to CONVERT it to a real date.

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[bluedragon2]

A couple rows of sample data would help us see which function or idea may be best...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[ericb1]

Thanks so much! There's 2 columns I'm concerned with here, ColA has a unique identifying number: 123456

ColB has the fixed with data:

OCF-1993 B & W-1989 UNR-1996 MANVILLE-1996 O-I-1998 FORTY-EIGHT INSULATION-1998 E-P1-2000 H.K. PORTER-2000 CELO-P1-2003 CELO-P2-2005

I need to get this out of 1 column, and into multiple columns so ColA would be ID: 123456 ColB would be OCF-1993 ColC would be B&W-1989, etc.

Make sense? Any help is appreciated, thanks!

 

[SkipVought]

"etc." is not a very specific requirement.

That would lead me to assume that I can delimit on [SPACE] character?
[tt]
OCF-1993
B
&
W-1989
UNR-1996
MANVILLE-1996
O-I-1998
FORTY-EIGHT
INSULATION-1998
E-P1-2000
H.K.
PORTER-2000
CELO-P1-2003
CELO-P2-2005
[/tt]

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[SkipVought]

Sorry, I jumped too soon.

Please explain the etc.

Can you use Data/text to columns - FIXED?

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[ericb1]

Sorry, I should have explained further: Each group of payment info is in a cell, and each row is different:

So the first cell is:

OCF-1993 B & W-1989 UNR-1996 MANVILLE-1996 O-I-1998 FORTY-EIGHT INSULATION-1998 E-P1-2000 H.K. PORTER-2000 CELO-P1-2003 CELO-P2-2005

But another cell is:

TEXACO-1998 FORTY-EIGHT INSULATION-1998 CHEVRON MARITIME-2002 E-P IRC F-2002 H.K. PORTER-2002 SHOOK & FLETCHER-2003 S & F 2-2003 MANVILLE-2003 S & F 3-2004 WORTHINGTON PUMP/HALLIBURTON-2005 WORTH-2 PUMP/HALLIBURTON-2006

They're different lenghts too as you can see. Text to columns fixed wants me to put column breaks in, but they wouldn't line up. Essentially, this is another database table that someone copied and pasted into excel cells. I'm trying to turn this into a relational database, so I need each payment in a separate field. (each cell is a series of payments: Texaco-1998, for example).

Thanks again.

 

[SkipVought]

So...

you can't delimit on length

you can't delimit on [SPACE]

you can't delimit on any other LOGICAL ELEMENT????...

yer up the crik, without a paddle, bud!

Looks like a job for...

Mr. Manuel Efforte!

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[ericb1]

Each payment has a hyphen, followed by 4 digits. Can I do something with that? After each hyphen count 4 positions, then move it to a new cell or something?

 

[SkipVought]

I found a pattern, I think!

Select the data. the results appear in the adjacent columns

Sub test()
    For Each r In Selection
        icol = 2
        p1 = 0
        a = Split(r.Value, " ")
        For i = 0 To UBound(a)
            If IsNumeric(Right(a(i), 4)) Then
                s = ""
                For j = p1 To i
                    s = s & a(j)
                Next
                Cells(r.Row, icol).Value = s
                p1 = i + 1
                icol = icol + 1
            End If
        Next
    Next
End Sub

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[SkipVought]

BTW,

It assumes that each column of data ends with a 4-digit number.

It assumes that the data is in column A.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[ericb1]

Thanks, but where do I put that code?

 

[SkipVought]

right-click the sheet tab and select View Code

One change...

Sub test()
    For Each r In Selection
        icol = Selection.Column + 1
        p1 = 0
        a = Split(r.Value, "  ")
        For i = 0 To UBound(a)
            If IsNumeric(Right(a(i), 4)) Then
                s = ""
                For j = p1 To i
                    s = s & a(j)
                Next
                Cells(r.Row, icol).Value = s
                p1 = i + 1
                icol = icol + 1
            End If
        Next
    Next
End Sub

Now your data column can be ANY column. Remember to SELECT the data. DResults will fill in to the right.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[ericb1]

Thanks so much, that worked great. Thanks again!