Seperate Alphabet and numbers in a cell
 

Hi,

Well I tried to seperate alphabets and numbers from a single cell but it has
to be done manually, the example of my data is "123456 klm" , "1294535 PTY",
"00000000018956 MO7 Payment Recived". I have almost 10000 enteries of such
data.

Thanks for your concern.

K****ij

Seperate Alphabet and numbers in a cell
 

And what should the output for your example be (especially the last one with
number on both sides of the alpha characters)?

--
Rick (MVP - Excel)


"K****ij" wrote in message
...
Hi,

Well I tried to seperate alphabets and numbers from a single cell but it
has
to be done manually, the example of my data is "123456 klm" , "1294535
PTY",
"00000000018956 MO7 Payment Recived". I have almost 10000 enteries of
such
data.

Thanks for your concern.

K****ij

Seperate Alphabet and numbers in a cell
 

You go to Menu Bar : DATA -- TEXT TO COLUMN. This function helps you to
seperate two groups of data in a cell. By using this function, you can
determine what is the delimeter like space, comma or column.

According to your sample of data, the delimeter is space


Here is the instruction
1. Highlight the column of data you want to seperate
2. go to Data -- Text To Colum, it will prompt you the 3-step instruction
3. At first step, you select "Delimeted" radio button, then hit Next
4. At second step, you check box of SPACE. (as your data delimeter is SPACE)
5. Hit FINISH button.

But, you have to spare an empty column next to the column where your data is
as well because the second group of data will be seperated and placed in the
next column.

Hope this help.

ToMMie

"K****ij" wrote:

Hi,

Well I tried to seperate alphabets and numbers from a single cell but it has
to be done manually, the example of my data is "123456 klm" , "1294535 PTY",
"00000000018956 MO7 Payment Recived". I have almost 10000 enteries of such
data.

Thanks for your concern.

K****ij

Seperate Alphabet and numbers in a cell
 

Thanks
Well the outcome is good enough if it can give only the first string of
numbers in a seperate cell, also it would be added advantage if it can give
the remaining text in different cell.

Ex : Cell 1= 00000000018956, Cell 2= MO7 Payment Recived

K****ij


"Rick Rothstein" wrote:

And what should the output for your example be (especially the last one with
number on both sides of the alpha characters)?

--
Rick (MVP - Excel)


"K****ij" wrote in message
...
Hi,

Well I tried to seperate alphabets and numbers from a single cell but it
has
to be done manually, the example of my data is "123456 klm" , "1294535
PTY",
"00000000018956 MO7 Payment Recived". I have almost 10000 enteries of
such
data.

Thanks for your concern.

K****ij

Seperate Alphabet and numbers in a cell
 

Well I have the Excel 2007 Version in which the Data -- TEXT TO COLUMN
option is not getting highlighted, What shall I do.....?

I tried it with my friends Comp, It has older version 2003 That has done
work for me but how to do that in 2007, if i require it next time......?



"ToMMie" wrote:

You go to Menu Bar : DATA -- TEXT TO COLUMN. This function helps you to
seperate two groups of data in a cell. By using this function, you can
determine what is the delimeter like space, comma or column.

According to your sample of data, the delimeter is space


Here is the instruction
1. Highlight the column of data you want to seperate
2. go to Data -- Text To Colum, it will prompt you the 3-step instruction
3. At first step, you select "Delimeted" radio button, then hit Next
4. At second step, you check box of SPACE. (as your data delimeter is SPACE)
5. Hit FINISH button.

But, you have to spare an empty column next to the column where your data is
as well because the second group of data will be seperated and placed in the
next column.

Hope this help.

ToMMie

"K****ij" wrote:

Hi,

Well I tried to seperate alphabets and numbers from a single cell but it has
to be done manually, the example of my data is "123456 klm" , "1294535 PTY",
"00000000018956 MO7 Payment Recived". I have almost 10000 enteries of such
data.

Thanks for your concern.

K****ij

Seperate Alphabet and numbers in a cell
 

If that space you show after the leading number in your examples is in every
number, then you can use this...

Cell 1: =LEFT(A1,FIND(" ",A1)-1)

Cell 2: =MID(A1,FIND(" ",A1)+1,255)

--
Rick (MVP - Excel)


"K****ij" wrote in message
...
Thanks
Well the outcome is good enough if it can give only the first string of
numbers in a seperate cell, also it would be added advantage if it can
give
the remaining text in different cell.

Ex : Cell 1= 00000000018956, Cell 2= MO7 Payment Recived

K****ij


"Rick Rothstein" wrote:

And what should the output for your example be (especially the last one
with
number on both sides of the alpha characters)?

--
Rick (MVP - Excel)


"K****ij" wrote in message
...
Hi,

Well I tried to seperate alphabets and numbers from a single cell but
it
has
to be done manually, the example of my data is "123456 klm" , "1294535
PTY",
"00000000018956 MO7 Payment Recived". I have almost 10000 enteries
of
such
data.

Thanks for your concern.

K****ij

Seperate Alphabet and numbers in a cell
 

Thanks for your help Rick, It Worked.



"Rick Rothstein" wrote:

If that space you show after the leading number in your examples is in every
number, then you can use this...

Cell 1: =LEFT(A1,FIND(" ",A1)-1)

Cell 2: =MID(A1,FIND(" ",A1)+1,255)

--
Rick (MVP - Excel)


"K****ij" wrote in message
...
Thanks
Well the outcome is good enough if it can give only the first string of
numbers in a seperate cell, also it would be added advantage if it can
give
the remaining text in different cell.

Ex : Cell 1= 00000000018956, Cell 2= MO7 Payment Recived

K****ij


"Rick Rothstein" wrote:

And what should the output for your example be (especially the last one
with
number on both sides of the alpha characters)?

--
Rick (MVP - Excel)


"K****ij" wrote in message
...
Hi,

Well I tried to seperate alphabets and numbers from a single cell but
it
has
to be done manually, the example of my data is "123456 klm" , "1294535
PTY",
"00000000018956 MO7 Payment Recived". I have almost 10000 enteries
of
such
data.

Thanks for your concern.

K****ij

Seperate Alphabet and numbers in a cell
 

Thanks Tommie..... it worked...


"ToMMie" wrote:

You go to Menu Bar : DATA -- TEXT TO COLUMN. This function helps you to
seperate two groups of data in a cell. By using this function, you can
determine what is the delimeter like space, comma or column.

According to your sample of data, the delimeter is space


Here is the instruction
1. Highlight the column of data you want to seperate
2. go to Data -- Text To Colum, it will prompt you the 3-step instruction
3. At first step, you select "Delimeted" radio button, then hit Next
4. At second step, you check box of SPACE. (as your data delimeter is SPACE)
5. Hit FINISH button.

But, you have to spare an empty column next to the column where your data is
as well because the second group of data will be seperated and placed in the
next column.

Hope this help.

ToMMie

"K****ij" wrote:

Hi,

Well I tried to seperate alphabets and numbers from a single cell but it has
to be done manually, the example of my data is "123456 klm" , "1294535 PTY",
"00000000018956 MO7 Payment Recived". I have almost 10000 enteries of such
data.

Thanks for your concern.

K****ij