Split cell values based on content

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, we have some cells which contain numbers and words eg. house number and
street name. We need to split the cell so that the numbers are in one column
and the street names are in another column. Is this possible? Thanks
 
Asuming 123 main st in column a1

in b1
=LEFT(A1,FIND(" ",A1)-1)
in c1
=MID(A1,FIND(" ",A1)+1,LEN(A1))
 
Hi Lance, thanks for that. it seems to do the job. Only problem is I've got a
list of hundreds of rows. Can I set the formula for the whole column without
having to click on each row and paste in the formula? it may be obvious but
i'm new to excel.
thanks
 
select the two formulas, do an edit copy, select the entire range you want to
copy to and do an edit paste
 

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

Split the cell 2
Sorting cells with a string formulas 1
Splitting information in cells 7
MID, LEFT, RIGHT help 5
Match Account ID from combining street names and numbers 1
Seperating Text from Numbers in a cell 13
Split data in field on character return 0
Word Create a Phone Book using MailMerge in word 2007 0

Back
Top