Tech Support Guy banner
Status
Not open for further replies.

split numbers & text in a single cell into 2 cells, xl2007

979 views 4 replies 2 participants last post by  KevinM5115 
#1 ·
Hi

I have a column of data which contains a reference followed by the customer name.

examples:-

Cell B1: 0111213729 rivett luke
Cell B2: 0122022157 fernandes de souza henrique
Cell B3: 0803141945 lopez fernandez eduardo

I am using excel 2007 and want to find the code to separate the number from B1 into cell C1 and the name in B1 into Cell D1.

I would be most grateful for any help with the appropriate formulas.

Many thanks
 
#2 ·
are the numbers always 10 digits
and do the numbers always have a space between the number and the customer

couple of ways

you could use a
=MID(B1,1,11)
=MID(B1,12,50)

if the number is variable but always the first space is between the name
=MID(B1,1,FIND(" ",B1,1))
=MID(B1,FIND(" ",B1,1)+1,50)

Rather than 50 - if the customer name is possibly longer - you could use the len() of the cell
=MID(B1,FIND(" ",B1,1)+1,LEN(B1))
 
#3 ·
Hi,

Thank you for the code i used the last option

=MID(B1,FIND(" ",B1,1)+1,LEN(B1))

and it has worked and put the customer name into a separate cell but i am still having difficulty putting the number into a separate cell. I presume it is a variation of the code to extract the name but i seem unable to create the variation that works. Please could you advise on that as well.


Many thanks
 
#5 ·
Hi,

Thank you for that it works.

However i have just discovered that if the number starts with a 0 I need to remove the 0 at the beginning as well in order for the following formula to work.

=IF(AND(B2>700000000,B2<999999999),(CONCATENATE(150,B2)),IF(AND(B2>1000000000,B2<1299999999),(CONCATENATE(15,B2)),IF(AND(B2>100000000,B2<399999999),(CONCATENATE(160,B2)),B2)))

This formula re-instates the complete number which was the original reference.

Any thoughts?
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top