Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
separation of text and numbers in an unmerged cell
How exactly text and numbers can be separated in an unmerged cell. There is
not a fixed number of digits in the available data so 'Text to column' option does not work. Please help. Thanks & Regards |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
separation of text and numbers in an unmerged cell
Supply some example of your data and how you want to seperate these items.
Text to column May work if you choose the delimted option, then hit next and selected spaces. "Tazeem" wrote: How exactly text and numbers can be separated in an unmerged cell. There is not a fixed number of digits in the available data so 'Text to column' option does not work. Please help. Thanks & Regards |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
separation of text and numbers in an unmerged cell
suppose u have value in A1
In B1 put this formula to separate number =MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$20),1 )), 0),COUNT(1*MID(A1,ROW($1:$20),1))) suppose in your data alphabets comes before the number then use this to separate the text from numbers. =MID(A1,ROW($1:$20),LEN(A1)-COUNT(1*MID(A1,ROW($1:$20),1))) suppose in your data number comes first before the alphabets then use this separate the text from numbers. =RIGHT(B2,LEN(B2)-COUNT(1*MID(B2,ROW(1:20),1))) note : ctrl+shift+enter once u enter the formula. On Sep 14, 4:28*pm, Tazeem wrote: How exactly text and numbers can be separated in an unmerged cell. There is not a fixed number of digits in the available data so 'Text to column' option does not work. Please help. Thanks & Regards |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
separation of text and numbers in an unmerged cell
Dear, example of data is as under:
SABIC (KSA) 114.500 Kingdom Holding (KSA) 8.000 664 1 3.4 0.0 Etisalat (UAE) 1 6.700 2 ,026 2 7.2 -2.9 Regards, Tazeem "Joel" wrote: Supply some example of your data and how you want to seperate these items. Text to column May work if you choose the delimted option, then hit next and selected spaces. "Tazeem" wrote: How exactly text and numbers can be separated in an unmerged cell. There is not a fixed number of digits in the available data so 'Text to column' option does not work. Please help. Thanks & Regards |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
separation of text and numbers in an unmerged cell
Which number are you look to get? It look like tyhe character portion of
theh string can be removed easily by using the closing parethesis. The number can then be seperated by using text to columns and using delimiter - Space as the seperator. A1 = "Kingdom Holding (KSA) 8.000 664 1 3.4 0.0" This formula will extract the company name into B1 Cell B1 =Left(A1,find(")",A1)-1) This will extract the number portion Cell C1 =mid(A1,find(")",A1)+1,len(B1)) You can remove the formula from the number portion by Copy then Paste Special - Values Once you have the number portion in a cell then you can use Text - to - columns method using Delimited - space to put each number in its own column. "Tazeem" wrote: Dear, example of data is as under: SABIC (KSA) 114.500 Kingdom Holding (KSA) 8.000 664 1 3.4 0.0 Etisalat (UAE) 1 6.700 2 ,026 2 7.2 -2.9 Regards, Tazeem "Joel" wrote: Supply some example of your data and how you want to seperate these items. Text to column May work if you choose the delimted option, then hit next and selected spaces. "Tazeem" wrote: How exactly text and numbers can be separated in an unmerged cell. There is not a fixed number of digits in the available data so 'Text to column' option does not work. Please help. Thanks & Regards |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
separation of text and numbers in an unmerged cell
On Sun, 14 Sep 2008 05:08:00 -0700, Tazeem
wrote: Dear, example of data is as under: SABIC (KSA) 114.500 Kingdom Holding (KSA) 8.000 664 1 3.4 0.0 Etisalat (UAE) 1 6.700 2 ,026 2 7.2 -2.9 Regards, Tazeem You could download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr Then use these formulas: A1: your data string B1: Initial text portion (ends with a <space followed by a digit or plus, minus or dot) =REGEX.MID($A1,"^.*?(?=\s[-+.\d])") C1: First set of numbers: =REGEX.MID($A1,"[-+]?\b\d*\.?\d+\b",COLUMNS($A:A)) Fill right as far as required to get all sets of numbers. (To I1 in your example) --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
separation of text and numbers in an unmerged cell
Dear Joel,
Actually I need just the name of companies which may be short or long, with or without brackets or any other mark etc. Simply the question is that can we separate the Text and numbers of a single cell. Waiting for reply, Thnaks & Regards "Joel" wrote: Supply some example of your data and how you want to seperate these items. Text to column May work if you choose the delimted option, then hit next and selected spaces. "Tazeem" wrote: How exactly text and numbers can be separated in an unmerged cell. There is not a fixed number of digits in the available data so 'Text to column' option does not work. Please help. Thanks & Regards |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
separation of text and numbers in an unmerged cell
It's no good saying "Waiting for reply"
You've been told about Data/ Text to columns/ delimited by spaces. If you don't give any clearer idea as to how your data items are separated, then you aren't going to get a clearer answer. Or are you trying to say that you may have ABC123DEFG6789HJK456 and that you want the ABCDEFGHJK in one cell and the 1236789456 in another cell? -- David Biddulph "Tazeem" wrote in message ... Dear Joel, Actually I need just the name of companies which may be short or long, with or without brackets or any other mark etc. Simply the question is that can we separate the Text and numbers of a single cell. Waiting for reply, Thnaks & Regards "Joel" wrote: Supply some example of your data and how you want to seperate these items. Text to column May work if you choose the delimted option, then hit next and selected spaces. "Tazeem" wrote: How exactly text and numbers can be separated in an unmerged cell. There is not a fixed number of digits in the available data so 'Text to column' option does not work. Please help. Thanks & Regards |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
separation of text and numbers in an unmerged cell
On Mon, 15 Sep 2008 06:25:02 -0700, Tazeem
wrote: Waiting for reply, And we who have replied are waiting for your comments on the already proffered solutions -- what happened when you tried them, and what was unsatisfactory about the results. --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
separation of text and numbers in an unmerged cell
Hi David,
I am sorry if you feel about my words 'waiting for reply', may be I don't know the art of effective communication. However, being the members of this community we have some relation and should ignore such things. Dear the example of data is as under: Saudi Cement21546 324. 255 SABIC (KSA)114.500 Kingdom Holding Company 8.000 664 1 3.4 0.0 Etisalat UAE, 1 6.700 2 ,026 2 7.2 -2.9 Now I want to separate just the name of companies which could be written in any style. Thanks & Regards "David Biddulph" wrote: It's no good saying "Waiting for reply" You've been told about Data/ Text to columns/ delimited by spaces. If you don't give any clearer idea as to how your data items are separated, then you aren't going to get a clearer answer. Or are you trying to say that you may have ABC123DEFG6789HJK456 and that you want the ABCDEFGHJK in one cell and the 1236789456 in another cell? -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i split an unmerged cell in two? | Excel Discussion (Misc queries) | |||
Retain value of merged cell in all unmerged cells | Excel Discussion (Misc queries) | |||
Imported text separation problem | Excel Discussion (Misc queries) | |||
select first cell in unmerged range | Excel Discussion (Misc queries) | |||
text and number separation | Excel Discussion (Misc queries) |