| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split numbers and text from a cell
Hi,
I have a column with cells containing codes formatted: XXXXnnnnnn (where X=letter and n= number) the number of letters is variable. I want to split the cells in one column with text and one clomn with numbers. Can anyone help me? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split numbers and text from a cell
Hi
try if the numeric part is always at the end: B1: Text part =LEFT(A1,LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq)-1) C1: numeric part: = --MID(A1, LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq),20) where seq is a defined name with the formula =ROW(INDIRECT("1:1024")) -- Regards Frank Kabel Frankfurt, Germany "Stefan" schrieb im Newsbeitrag ... Hi, I have a column with cells containing codes formatted: XXXXnnnnnn (where X=letter and n= number) the number of letters is variable. I want to split the cells in one column with text and one clomn with numbers. Can anyone help me? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split numbers and text from a cell
I tried this suggestion, but there seems to be an error in it, I don't get
it where the problem is. "Frank Kabel" wrote in message ... Hi try if the numeric part is always at the end: B1: Text part =LEFT(A1,LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq)-1) C1: numeric part: = --MID(A1, LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq),20) where seq is a defined name with the formula =ROW(INDIRECT("1:1024")) -- Regards Frank Kabel Frankfurt, Germany "Stefan" schrieb im Newsbeitrag ... Hi, I have a column with cells containing codes formatted: XXXXnnnnnn (where X=letter and n= number) the number of letters is variable. I want to split the cells in one column with text and one clomn with numbers. Can anyone help me? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split numbers and text from a cell
Hi
have you defined the seq name (menu 'Insert - Name - Define')? -- Regards Frank Kabel Frankfurt, Germany "Stefan" schrieb im Newsbeitrag ... I tried this suggestion, but there seems to be an error in it, I don't get it where the problem is. "Frank Kabel" wrote in message ... Hi try if the numeric part is always at the end: B1: Text part =LEFT(A1,LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq)-1) C1: numeric part: = --MID(A1, LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq),20) where seq is a defined name with the formula =ROW(INDIRECT("1:1024")) -- Regards Frank Kabel Frankfurt, Germany "Stefan" schrieb im Newsbeitrag ... Hi, I have a column with cells containing codes formatted: XXXXnnnnnn (where X=letter and n= number) the number of letters is variable. I want to split the cells in one column with text and one clomn with numbers. Can anyone help me? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split numbers and text from a cell
Yes, I did, when I enter =seq in a cell, it returns the value 1, so the
defined name seq is active. Regards, Stefan "Frank Kabel" wrote in message ... Hi have you defined the seq name (menu 'Insert - Name - Define')? -- Regards Frank Kabel Frankfurt, Germany "Stefan" schrieb im Newsbeitrag ... I tried this suggestion, but there seems to be an error in it, I don't get it where the problem is. "Frank Kabel" wrote in message ... Hi try if the numeric part is always at the end: B1: Text part =LEFT(A1,LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq)-1) C1: numeric part: = --MID(A1, LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq),20) where seq is a defined name with the formula =ROW(INDIRECT("1:1024")) -- Regards Frank Kabel Frankfurt, Germany "Stefan" schrieb im Newsbeitrag ... Hi, I have a column with cells containing codes formatted: XXXXnnnnnn (where X=letter and n= number) the number of letters is variable. I want to split the cells in one column with text and one clomn with numbers. Can anyone help me? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split numbers and text from a cell
Hi
just tested the formulas again and they work. What error do you get exactly and what is your value in the reffered cell? -- Regards Frank Kabel Frankfurt, Germany "Stefan" schrieb im Newsbeitrag ... Yes, I did, when I enter =seq in a cell, it returns the value 1, so the defined name seq is active. Regards, Stefan "Frank Kabel" wrote in message ... Hi have you defined the seq name (menu 'Insert - Name - Define')? -- Regards Frank Kabel Frankfurt, Germany "Stefan" schrieb im Newsbeitrag ... I tried this suggestion, but there seems to be an error in it, I don't get it where the problem is. "Frank Kabel" wrote in message ... Hi try if the numeric part is always at the end: B1: Text part =LEFT(A1,LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq)-1) C1: numeric part: = --MID(A1, LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq),20) where seq is a defined name with the formula =ROW(INDIRECT("1:1024")) -- Regards Frank Kabel Frankfurt, Germany "Stefan" schrieb im Newsbeitrag ... Hi, I have a column with cells containing codes formatted: XXXXnnnnnn (where X=letter and n= number) the number of letters is variable. I want to split the cells in one column with text and one clomn with numbers. Can anyone help me? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split numbers and text from a cell
On Sun, 24 Oct 2004 12:56:11 +0200, "Stefan" wrote:
Hi, I have a column with cells containing codes formatted: XXXXnnnnnn (where X=letter and n= number) the number of letters is variable. I want to split the cells in one column with text and one clomn with numbers. Can anyone help me? One way with *array* formulas: (To enter an *array* formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula). Letters: =LEFT($A1,MATCH(TRUE,ISNUMBER(--MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)),0)-1) Numbers: =MID($A1,MATCH(TRUE,ISNUMBER(--MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)),0),255) (Feel free to change the reference style of the range reference to your requirements). --ron |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split numbers and text from a cell
Another one... =REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")-1),"") which must be confirmed with control+shift+enter instead of just with enter. This yields the numeric part of the string in text format. The result can be coerced into a true number by double negating the formula, that is, =--REPLACE(...) The non-digit part can be obtained by: =SUBSTITUTE(A1,B1,"") where B1 houses the REPLACE formula. Stefan Wrote: Hi, I have a column with cells containing codes formatted: XXXXnnnnnn (where X=letter and n= number) the number of letters is variable. I want to split the cells in one column with text and one clomn with numbers. Can anyone help me? -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=271891 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| split text and numbers into separate cells | Excel Discussion (Misc queries) | |||
| Split numbers and text | Excel Discussion (Misc queries) | |||
| Split numbers and text from 1 column into 2 columns | Excel Discussion (Misc queries) | |||
| Split numbers in cell | Excel Worksheet Functions | |||
| How do I split a column having numbers and text in a random order | Excel Worksheet Functions | |||
![Stefan[_8_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
