Replacing Text in a cell with blanks
 

I have a spreadsheet in excel 2003. In cell a1 I have a name for
example:Smith,Lisa
In cell B2 I have an address and a phone number
for example
1234 Anywhere st
Akron,Ohio 44332
330-555-1212

I need to eliminate the phone number from the second field in order to use
it as the basis for a mail merge
In other words I only want the street, city and state left without the phone
number

I can't figure out how to do that successfully. The street
then the city state and zip
and finally the phone are on different lines in the same field

Can you put me in the right direction? Thanks so much

Replacing Text in a cell with blanks
 

=LEFT(B2,FIND("-",B2,LEN(B2)-12)-4)


"Tom" wrote:

I have a spreadsheet in excel 2003. In cell a1 I have a name for
example:Smith,Lisa
In cell B2 I have an address and a phone number
for example
1234 Anywhere st
Akron,Ohio 44332
330-555-1212

I need to eliminate the phone number from the second field in order to use
it as the basis for a mail merge
In other words I only want the street, city and state left without the phone
number

I can't figure out how to do that successfully. The street
then the city state and zip
and finally the phone are on different lines in the same field

Can you put me in the right direction? Thanks so much

Replacing Text in a cell with blanks
 

On Sat, 9 May 2009 14:25:01 -0700, Tom wrote:

I have a spreadsheet in excel 2003. In cell a1 I have a name for
example:Smith,Lisa
In cell B2 I have an address and a phone number
for example
1234 Anywhere st
Akron,Ohio 44332
330-555-1212

I need to eliminate the phone number from the second field in order to use
it as the basis for a mail merge
In other words I only want the street, city and state left without the phone
number

I can't figure out how to do that successfully. The street
then the city state and zip
and finally the phone are on different lines in the same field

Can you put me in the right direction? Thanks so much

This formula will return everything up to the second LineFeed in that string:

=LEFT(B2,FIND(CHAR(1),SUBSTITUTE(B2,CHAR(10),CHAR( 1),2))-1)

--ron