How to import txt???

J

JDS

I have worked with importing, and text-to-column process but I can't seem to
parse this the way I need it. I've pasted data below and perhaps someon can
give me a "how-to" if possible. Thanks. The place I need the column break
is just before the number.

Sonic: Hurbano 875
Sonic: Latin Contemporary 871
Sonic: Latin Jazz 879
Sonic: Musica De Las Americas 872
Sonic: Rock en Espanol 878
Sonic: Salsa 874
XM: 1st Wave 839
XM: Alt. Nation 834
XM: Area 858
XM: BPM 859
XM: Bluegrass Junction 812
XM: Bluesville 854
XM: Boneyard 830
XM: Chrome 861
XM: Cinemagic 822
XM: Classic Rewind 837
XM: Classic Vinyl 833
XM: Deep Tracks 840
XM: Enlighten 828
XM: Escape 820
XM: Fine Tuning 855
XM: Hair Nation 829
XM: Heart & Soul 843
XM: Hip Hop Nation 847
XM: Kids Place Live 868
XM: Liquid Metal - XL 841
XM: Lithium 838
XM: Octane 835
XM: On Broadway 823
XM: Outlaw Country 810
XM: Pop2K 817
XM: Praise 827
XM: Prime Country 809
XM: Radio Disney 867
XM: Real Jazz 850
XM: Roadhouse 808
XM: Sirius XM Chill 857
XM: Sirius XM Love 819
XM: Sirius XM Pops 866
XM: Sirius XM U 831
XM: Soul Town 842
XM: Spa 856
XM: Symphony Hall 864
XM: The 40s 801
XM: The 50s 802
XM: The 60s 803
XM: The 70s 804
XM: The 80s 805
XM: The 90s 806
XM: The Blend 821
XM: The Bridge 849
XM: The Coffeehouse 829
XM: The Groove 844
XM: The Heat 825
XM: The Highway 825
XM: The Joint 863
XM: The Loft 836
XM: The Message 826
XM: The Pulse 818
XM: The Spectrum 832
XM: The Village 813
XM: Top 20 on 20 816
XM: Watercolors 851
XM: Willie's Place 811
 
J

JDS

Actually let me give a better example where I need 3 columns - Title - HD -
Channel. If no value for HD, the filed would be blank.

A&E HD 265
ABC Family HD 311
Altitude Sports and Entertainment HD 681
American Movie Classics (AMC) 254
Animal Planet HD 282
BBC America 264
BET Jazz 330
BYU TV 374
Big Ten Network HD 220
Black Entertainment Television (BET) 329
Bloomberg Television 353
Boomerang 298

Should look like:
A&E HD 265
ABC Family HD 311
Altitude Sports and Entertainment HD 681
American Movie Classics (AMC) 254
Animal Planet HD 282
BBC America 264
 
J

JDS

wow - ya got me on this!?

note, i posted a follow up with more specific example of situation.
 
B

Bob I

Humm, that second sample is somewhat more complex would require parsing
out the HD. Perhaps someone else will sort that one.
 
S

Student

B1: =IF(ISERROR(FIND("HD",A1)),LEFT(A1,LEN(A1)-4),LEFT(A1,FIND
("HD",A1)-1))
C1: =IF(ISERROR(FIND("HD",A1)),"",MID(A1,FIND("HD",A1),2))
D1: =RIGHT(A1,3)
 
J

JDS

Anyone help me with this please?

I need 3 columns - Title - HD - Channel. If no value for HD, the field
would be blank.

Data looks like this in txt file:
A&E HD 265
ABC Family HD 311
Altitude Sports and Entertainment HD 681
American Movie Classics (AMC) 254
Animal Planet HD 282
BBC America 264
BET Jazz 330
BYU TV 374
Big Ten Network HD 220
Black Entertainment Television (BET) 329
Bloomberg Television 353
Boomerang 298
Click to expand...

Needs to look like this in Excel
 
S

Student

Have you tried my previous suggestion? Below is slightly improved
version:

1. Copy paste your text into A1.
2. Enter in B1: =IF(ISERROR(FIND(" HD ",A1)),LEFT(A1,LEN(A1)-4),LEFT
(A1,FIND(" HD ",A1)-1))
3. Enter in C1: =IF(ISERROR(FIND(" HD ",A1)),"",TRIM(MID(A1,FIND(" HD
",A1),3)))
4. Enter in D1: =RIGHT(A1,3)

*Notice the space between HD and quotation marks in above formulas.

Drag down b1:d1 until the last row of your range. This should split it
the way you want it.
 
J

JDS

still not getting it.

B1 has A&E HD in it. C1 is blank. D1 has 265.
B1 should read A&E. C1 should read HD. D1 is ok with 265.


Have you tried my previous suggestion? Below is slightly improved
version:

1. Copy paste your text into A1.
2. Enter in B1: =IF(ISERROR(FIND(" HD ",A1)),LEFT(A1,LEN(A1)-4),LEFT
(A1,FIND(" HD ",A1)-1))
3. Enter in C1: =IF(ISERROR(FIND(" HD ",A1)),"",TRIM(MID(A1,FIND(" HD
",A1),3)))
4. Enter in D1: =RIGHT(A1,3)

*Notice the space between HD and quotation marks in above formulas.

Drag down b1:d1 until the last row of your range. This should split it
the way you want it.
 
S

Student

Check how HD is written in A&E HD 265 Is there a space between H and
D? Where the other rows split correctly? I checked on my side: as long
as HD is written consistently without the space between H and D, but
with the space before and after HD, the formulas should work fine. If
after checking it still doesn't work, paste again your data in your
next post exactly as it appears in your Excel worksheet.
 
J

JDS

There is no space between H and D - it reads HD. It does have a space
before and after.

Do you suppose copying/pasting the formula is causing the problem?

Here is a sample post - all data is in column A.

A&E HD 265
ABC Family HD 311
Altitude Sports and Entertainment HD 681
American Movie Classics (AMC) 254
Animal Planet HD 282
BBC America 264
BET Jazz 330
BYU TV 374
Big Ten Network HD 220
Black Entertainment Television (BET) 329
Bloomberg Television 353
Boomerang 298
Bravo HD 273
CMT HD 327
CNBC HD 355
CNBC World 357
CNN HD 202
CSPAN 1 350
CSPAN 2 351
Cartoon Network HD 296
Cartoon Network (West) 297
Chiller 257
Christian Television Network (CTN) 376
Comedy Central 249

Check how HD is written in A&E HD 265 Is there a space between H and
D? Where the other rows split correctly? I checked on my side: as long
as HD is written consistently without the space between H and D, but
with the space before and after HD, the formulas should work fine. If
after checking it still doesn't work, paste again your data in your
next post exactly as it appears in your Excel worksheet.
 
S

Student

I pasted the data you posted into A1, then entered those three
formulas into b1,c1,d1, dragged b1:d1 down to the last data row, and
everything split correctly. It is possible that copy/paste of formulas
from the post messes something up, because long formulas wrap around
in the post, but I couldn't reproduce it in several scenarios I tried.
I suggest:

1. Enter formulas by hand. Here they are again:

B1:

=IF(ISERROR(FIND(" HD ",A1)),LEFT(A1,LEN(A1)-4),LEFT(A1,FIND(" HD
",A1)-1))

C1:

=IF(ISERROR(FIND(" HD ",A1)),"",TRIM(MID(A1,FIND(" HD",A1),3)))

D1:

=RIGHT(A1,3)

If it stll doesn't work, send me an email with your spreadsheet
attached to (e-mail address removed).
 
R

Ron Rosenfeld

Actually let me give a better example where I need 3 columns - Title - HD -
Channel. If no value for HD, the filed would be blank.

A&E HD 265
ABC Family HD 311
Altitude Sports and Entertainment HD 681
American Movie Classics (AMC) 254
Animal Planet HD 282
BBC America 264
BET Jazz 330
BYU TV 374
Big Ten Network HD 220
Black Entertainment Television (BET) 329
Bloomberg Television 353
Boomerang 298

Should look like:
A&E HD 265
ABC Family HD 311
Altitude Sports and Entertainment HD 681
American Movie Classics (AMC) 254
Animal Planet HD 282
BBC America 264
Click to expand...

You can do it with formulas, or with a VBA Macro.

Here are formulas to parse things out:

Title:
=IF(ISNUMBER(FIND(" HD ",A1)),TRIM(LEFT(A1,FIND(" HD ",A1))),
TRIM(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1," ","")))))))

HD:
=IF(ISNUMBER(FIND(" HD ",A1)),"HD","")

Channel:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
--ron
 
J

JDS

thanks I'll try it be hand and let you know.

I pasted the data you posted into A1, then entered those three
formulas into b1,c1,d1, dragged b1:d1 down to the last data row, and
everything split correctly. It is possible that copy/paste of formulas
from the post messes something up, because long formulas wrap around
in the post, but I couldn't reproduce it in several scenarios I tried.
I suggest:

1. Enter formulas by hand. Here they are again:

B1:

=IF(ISERROR(FIND(" HD ",A1)),LEFT(A1,LEN(A1)-4),LEFT(A1,FIND(" HD
",A1)-1))

C1:

=IF(ISERROR(FIND(" HD ",A1)),"",TRIM(MID(A1,FIND(" HD",A1),3)))

D1:

=RIGHT(A1,3)

If it stll doesn't work, send me an email with your spreadsheet
attached to (e-mail address removed).
 
Top