How to parse "X at Y" into two columns, X Y

[Leo Bueno]

I used to have a Lotus 123 spreadsheet formula which parsed college
football matchups into two columns, so that, for example

California at Air Force
Michigan State at Rutgers
South Carolina at Vanderbilt
Oklahoma State at UCLA

which in effect are records with two fields (with "at" as a delimiter)
would create two colums

California Air Force
Michigan State Rutgers
South Carolina Vanderbilt
Oklahoma State UCLA

Will appreciate some tips on a simple analogous Excel formula.

Thanks.


--
=================================================
Do you like wine? Do you live in South Florida?
Visit the MIAMI WINE TASTERS group at
http://groups.yahoo.com/group/miamiWINE
=================================================

 

[Don Guillett]

try this. Then just delete the original column.
Sub parseat()
For Each c In Selection
x = InStr(c, " at") - 1
'MsgBox x
c.Offset(, 1) = Left(c, x)
c.Offset(, 2) = Right(c, Len(c) - x - 4)
Next
End Sub

 

[Leo Bueno]

try this. Then just delete the original column.
Sub parseat()
For Each c In Selection
x = InStr(c, " at") - 1
'MsgBox x
c.Offset(, 1) = Left(c, x)
c.Offset(, 2) = Right(c, Len(c) - x - 4)
Next
End Sub

Not terribly well-versed with macros. What about with worksheet
formulas?

Thanks.

--
=================================================
Do you like wine? Do you live in South Florida?
Visit the MIAMI WINE TASTERS group at
http://groups.yahoo.com/group/miamiWINE
=================================================

 

[Jason Weiss]

Another way to do it without macros would be to use the Search function,
which returns the position of the first character in the search string.

If A1 contains your matchup, e.g., "Michigan State at Bowling Green", enter
the following
Cell B1: =LEFT(A1,SEARCH(" at ",A1,1)-1)
Cell C1: =RIGHT(A1,LEN(A1)-(SEARCH(" at ",A1,1)+3))

The only downside to this method compared to Don's is that you can't delete
the cells with the full matchup text.

....Jay

 

[Don Guillett]

Excellent formulas. Just copy and paste values and then delete original
column

 

[Dave Peterson]

One more option:
Edit|Replace
what: _at_ (underscore means spacebar)
with: | (vertical bar)

Then insert a column to the right and do Data|text to columns, delimited by |.

 

[Leo Bueno]

One more option:
Edit|Replace
what: _at_ (underscore means spacebar)
with: | (vertical bar)

Then insert a column to the right and do Data|text to columns, delimited by |.

I tried that (using a different delimiter, *). The problem is that I
use an expression that picks one of the two teams and when I did the
data-to-text parsing, the formula shows #REF# in both the formula's
own syntax and as the output.

In other words, the column (call it T) where the better team is picked
has the following formula
IF(J4>0,A4,B4)
Resulting in the name of the team in column A or column B being
selected as the output in column T.

I paste the XXX at YYY entries to column A. When I do the
text-to-colum parsing, the entries in columns A and B get replaced
with the name of each team; so far so good. However, the formula in
the T column changes!!! It becomes
IF(J4>0,#REF#,#REF#)
and produces #REF# as the output.







--
=================================================
Do you like wine? Do you live in South Florida?
Visit the MIAMI WINE TASTERS group at
http://groups.yahoo.com/group/miamiWINE
=================================================

 

[Leo Bueno]

Elegant formula. Worked, as the cliche goes, like a charm.

Thanks.

Another way to do it without macros would be to use the Search function,
which returns the position of the first character in the search string.

If A1 contains your matchup, e.g., "Michigan State at Bowling Green", enter
the following
Cell B1: =LEFT(A1,SEARCH(" at ",A1,1)-1)
Cell C1: =RIGHT(A1,LEN(A1)-(SEARCH(" at ",A1,1)+3))

The only downside to this method compared to Don's is that you can't delete
the cells with the full matchup text.

...Jay

--
=================================================
Do you like wine? Do you live in South Florida?
Visit the MIAMI WINE TASTERS group at
http://groups.yahoo.com/group/miamiWINE
=================================================

 

[Dave Peterson]

It sounds like you got it working using formulas, but it sounds like if you
reentered the formula (and dragged down??), it would have worked ok.