# separate numbers from rest of the text

#### andonny

##### Board Regular
Hi,
I need to separate the numbers which appear at the end of a text and they are in brackets. I usually use the function below to do that. Latley somebody started to add numbers into two brackets and then it doesn't work anymore.

=MID(A1,FIND("(",A1,1)+1,LEN(A1)-FIND("(",A1,1)-1)

These are the different type of text which can be in cells.

Abc test1(12) (1234)
bbc test2 (1) (12345)
ddd test2 (123456)
etc.

Result:
1234
12345
123456

Your help is very much appreciated
Andonny

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hello Andonny

try this used defined function
press alt-f11 and copy the code below in a new module

<pre>
Option Explicit

Function GetValue(str As String) As Double
Dim n As Integer
For n = 1 To Len(str)
If IsNumeric(Mid(str, n, 1)) Then GetValue = GetValue & Mid(str, n, 1)
Next
End Function
</pre>

example
getvalue(a1)
where a1 contains the text

hth

Hi,
Thank you very much for your reply. I tested the function and I noticed that it lists all the numbers in the text. I really need only the numbers which are in the last set of brackets.
Like if I have "test(12) (1234)" then I need only to display 1234. Some of the text looks like "test1 (12345)" and then it should display 12345.

Thanks a million
Andonny

=LEFT(RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1,"(","@",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))),LEN(RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1,"(","@",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))))-1)

Be sure there are no spaces after the last ) in column A.

Morefunc Add-in includes a function that may help.

=WMID(WMID(A2,-1,1,CHAR(40)),1,1,CHAR(41))*

The *1 converts the text to a number value.

Morefunc includes a variety of very useful functions.

http://longre.free.fr/english/index.html

This message was edited by Dave Patton on 2002-09-23 08:51

On 2002-09-23 02:15, andonny wrote:
Hi,
I need to separate the numbers which appear at the end of a text and they are in brackets. I usually use the function below to do that. Latley somebody started to add numbers into two brackets and then it doesn't work anymore.

=MID(A1,FIND("(",A1,1)+1,LEN(A1)-FIND("(",A1,1)-1)

These are the different type of text which can be in cells.

Abc test1(12) (1234)
bbc test2 (1) (12345)
ddd test2 (123456)
etc.

Result:
1234
12345
123456

Your help is very much appreciated
Andonny

A great occasion for you to install the morefunc.xll add-in from:

http://longre.free.fr/english/index.html

and use this fast formula:

=-(WMID(A1,WORDCOUNT(A1))+0)

On 2002-09-23 08:37, Aladin Akyurek wrote:
On 2002-09-23 02:15, andonny wrote:
Hi,
I need to separate the numbers which appear at the end of a text and they are in brackets. I usually use the function below to do that. Latley somebody started to add numbers into two brackets and then it doesn't work anymore.

=MID(A1,FIND("(",A1,1)+1,LEN(A1)-FIND("(",A1,1)-1)

These are the different type of text which can be in cells.

Abc test1(12) (1234)
bbc test2 (1) (12345)
ddd test2 (123456)
etc.

Result:
1234
12345
123456

Your help is very much appreciated
Andonny

A great occasion for you to install the morefunc.xll add-in from:

http://longre.free.fr/english/index.html

and use this fast formula:

=-(WMID(A1,WORDCOUNT(A1))+0)

Dave

On 2002-09-23 12:37, sen_edp wrote:
hello everyone [...]

If there isn't a space between the parens i
get error, can any one help ?

WMID as well as WORDCOUNT expects a seperator/delimiter between "words", a space per default.

If you have entries like

1234xweq
23swtyeri

or

qwtasy123
qwe1234567

then use the formula Albert quoted.

If you have a delimiter between the unwanted part and the wanted part of a string, use WMID (combined with WORDCOUNT).

You can of course preprocess Andonny's input values using SUBSTITUTE to remove the delimiters and apply the formula Albert quoted.

This message was edited by Aladin Akyurek on 2002-09-23 13:22

Replies
12
Views
668
Replies
8
Views
462
Replies
3
Views
784
Replies
14
Views
3K
Replies
3
Views
662

1,218,746
Messages
6,144,264
Members
450,533
Latest member
xoxo1998

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back