separate numbers from rest of the text

andonny

Board Regular
Joined
Mar 11, 2002
Messages
220
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 shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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
 
Upvote 0
Using Aladin Akyurec's formula inthis topic (thank you Aladin):

=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.
 
Upvote 0
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.
Download the morefunc add-in from

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

and install/activate the add-in via Tools|Add-Ins.
This message was edited by Dave Patton on 2002-09-23 08:51
 
Upvote 0
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)
 
Upvote 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)

Excellent Aladin. Much neater.

Dave
 
Upvote 0
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.

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

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top