Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
# symbols replacing text in a cell | Excel Discussion (Misc queries) | |||
Macro - Fixed text code needs replacing with variable text | Excel Discussion (Misc queries) | |||
replacing text in a cell | Excel Discussion (Misc queries) | |||
Concatenate cells, replacing blanks with cell contents from other rows | Excel Worksheet Functions | |||
INTRICATE PROBLEM- How to find multiple text,excluding "H", in a multiple range of cells, then replacing 0 with another number in another cell | Excel Worksheet Functions |