separation of text and numbers in an unmerged cell

[Tazeem]

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

 

[Joel]

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.

 

[muddan madhu]

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.

 

[Tazeem]

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]

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.

 

[Ron Rosenfeld]

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

 

[Tazeem]

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

 

[David Biddulph]

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?

 

[Ron Rosenfeld]

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

 

[Tazeem]

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