Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I seperate numbers from text (in one cell) into 2 cells? | Excel Discussion (Misc queries) | |||
Adding numbers in one cell and showing total in seperate cell | Excel Discussion (Misc queries) | |||
associate alphabet letters with numbers? | New Users to Excel | |||
alphabet as Numbers? | Excel Discussion (Misc queries) | |||
SEPERATE NUMBERS IN A CELL | Excel Worksheet Functions |