Spliting cell contents into two cels based on location of "&"

B

Brad

I have exported a list of push pins from Microsoft MapPoint 2004 that
represent intersections. Column B has text in it such as, "Main St & First
St." Is there a way to separate the two street names based on the "&" in to
two columns. I know how to concatenate them, but how do I spilt them?

Brad

Excel 2002 on XP Pro SP 3
Excel 2007 on Vista 64
 
C

Chip Pearson

To get the text to the left of the & character, use

=LEFT(A1,FIND("&",A1)-2)

To get the text to the right of the & character, use

=MID(A1,FIND("&",A1)+2,99)

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional formating on text contents 4
Sheet Numbering Routine 0
Copy contents of one cell to another based on matching in 2 other 1
Formatting one cell based on the contents of another cell 2
Formatting one cell based on the contents of another 4
Select & deleting all rows based on the contents of a cell in the 7
Need to find a row in a worksheet based on the value of a cell 1
Reletive Referencing of Another Cell's Formulas 2

Top