Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Split cell values based on content
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 |
#2
|
|||
|
|||
Take a look at Data Text to columns.
HTH Jason Atlanta, GA -----Original Message----- 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 . |
#3
|
|||
|
|||
Asuming 123 main st in column a1
in b1 =LEFT(A1,FIND(" ",A1)-1) in c1 =MID(A1,FIND(" ",A1)+1,LEN(A1)) "mel" wrote: 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 |
#4
|
|||
|
|||
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 "LanceB" wrote: Asuming 123 main st in column a1 in b1 =LEFT(A1,FIND(" ",A1)-1) in c1 =MID(A1,FIND(" ",A1)+1,LEN(A1)) "mel" wrote: 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 |
#5
|
|||
|
|||
select the two formulas, do an edit copy, select the entire range you want to
copy to and do an edit paste "mel" wrote: 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 "LanceB" wrote: Asuming 123 main st in column a1 in b1 =LEFT(A1,FIND(" ",A1)-1) in c1 =MID(A1,FIND(" ",A1)+1,LEN(A1)) "mel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Toggle multiple values in single cell | Excel Worksheet Functions | |||
Can an excel cell automatically change fill colors based on values | Excel Discussion (Misc queries) | |||
make a cell empty based on condition | Charts and Charting in Excel | |||
Transferring cell content between workbooks using cell references | Excel Discussion (Misc queries) | |||
Modify Row & Cell Contents based upon Cells Values | Excel Worksheet Functions |