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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

sen_edp

Well-known Member
Joined
Mar 13, 2002
Messages
555
Office Version
  1. 365
Platform
  1. Windows
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
 

andonny

Board Regular
Joined
Mar 11, 2002
Messages
220
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
 

Albert 1

Active Member
Joined
Feb 22, 2002
Messages
393
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.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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 Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

Forum statistics

Threads
1,144,363
Messages
5,723,914
Members
422,527
Latest member
JayTheKaz

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
Top