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
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
Would it be possible to use the "Text to Columns" from the Data menu?
So that:
Original Cell Content: A1=1234 Wysteria Lane
-would change to-
A1=1234
B1=Wysteria
C1=Lane
You could then CONCATENATE cells B1 and C1, so that:
D1 would be: =CONCATENATE(B1&" "&C1)
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
>.
>
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
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
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
Hi, If I have cells in a column, each with a different 9 digit number, (SS), how do I reduce each to the final 4 digits? I tried to use =Right(345678901,4). That works in the first cell, but I cannot get the formula to copy using the number in each cell.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks