Extracting number from Text and ()

[Guest]

In my worksheet, I have a series of strings that contain text, numbers and ().

For example,
In cell A1, string of text L(117),D(93),O(5).
In cell A2, string of text L(6),D(117),O(20)
In cell A3, string of text GRTR1%orYM(119),O(3).

The end result for each cell would be:

Column 1 Column 2 Column 3
Column 4
Result from Cell A1 117 93 5
Result from Cell A2 6 117 20
Result from Cell A3 3
119

Can anyone help me with a formula that produces the above results?
Any help is much appreciated !!

 

[Guest]

This custom function will get the answers you are looking for

call it with
=getval(E2,3) where E2 is the string with numbers and digits and 3 is which
numberic string you want to extract. In your example make the 2nd parameter
1 in column 1; 2nd parameter 2 in column 2; and 2nd parameter 3 in column 3


Function getval(InputString As String, Index As Integer)

MyString = InputString
charcount = 1
InputLength = Len(InputString)
For i = 1 To Index

'Remove Non-Numeric digits
Do While (charcount <= InputLength) And _
((Mid(MyString, charcount, 1) < "0") Or _
(Mid(MyString, charcount, 1) > "9"))

charcount = charcount + 1
Loop

If charcount > InputLength Then Exit For

'Get Non-Numeric digits
MyNumber = ""

Do While (charcount <= InputLength) And _
(Mid(MyString, charcount, 1) >= "0") And _
(Mid(MyString, charcount, 1) <= "9")

MyNumber = MyNumber + Mid(MyString, charcount, 1)
charcount = charcount + 1

Loop
If charcount > InputLength Then Exit For


Next i
getval = MyNumber


End Function

 

[Guest]

Please do not multipost but make it clear in the first post what you want.

I assume you are extracting the data from another piece of software - can
you change any of the format in that software?

to do this one you need to change the % to ,,, and then follow the
instructions in my other post.

If you are doing this on a regular basis then record a macro when you do
this the first time
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Lori]

Try Data > Text to Columns > Comma delimited.
Then do an Edit > Replace "*(" with "(" and change the sign.

 

[Ron Rosenfeld]

In my worksheet, I have a series of strings that contain text, numbers and ().

For example,
In cell A1, string of text L(117),D(93),O(5).
In cell A2, string of text L(6),D(117),O(20)
In cell A3, string of text GRTR1%orYM(119),O(3).

The end result for each cell would be:

Column 1 Column 2 Column 3
Column 4
Result from Cell A1 117 93 5
Result from Cell A2 6 117 20
Result from Cell A3 3
119

Can anyone help me with a formula that produces the above results?
Any help is much appreciated !!

Here's one way with formulas:

Download and install Longre's free and easily distributable morefunc.xll add-in
from http://xcell05.free.fr

Then use this Regular Expression formula:

A2: =REGEX.MID($A1,"(?<=\()\d+(?=\))",COLUMNS($A:A))

Copy/drag across to column D
Copy/drag down as far as needed.

It picks out sequential integer numbers that are delineated by parentheses.

With regard to your A3 example, I note that you do NOT show the "1" as being
extracted, and I'm not sure of which columns for the location of 3 and 119. I
put them in the first two columns. If this is wrong, please post back with
more detail.
--ron

 

[Guest]

Using the example in cell A1: L(117),D(93),O(5)

I should have mentioned that Column B should only extract numbers associated
with L. So for A1, Column B1 will show 117.

Column C should only extract numbers associated with D. So for A1, Column
C1 will show 93.

Column D should only extract numbers associated with O. so for A1, Column
D1 will show 5.

Using the example from cell A3. GRTR1%orYM(119),O(3)
Column E should only extract numbers associated with YM. So for A3, Column
E1 will show 119.

The length of my numbers varies.

Any help is much appreciated

 

[Ron Rosenfeld]

Using the example in cell A1: L(117),D(93),O(5)

I should have mentioned that Column B should only extract numbers associated
with L. So for A1, Column B1 will show 117.

Column C should only extract numbers associated with D. So for A1, Column
C1 will show 93.

Column D should only extract numbers associated with O. so for A1, Column
D1 will show 5.

Using the example from cell A3. GRTR1%orYM(119),O(3)
Column E should only extract numbers associated with YM. So for A3, Column
E1 will show 119.

The length of my numbers varies.

Any help is much appreciated

I would still use the morefunc.xll add-in I previously recommended.

I continue to assume that each letter association (L D O YM) is followed by an
"open-parenthesis" "(", and that each numeric value is an integer.

If either of the above is not the case, the regex would need to be changed.

I would recommend the following:

Have your strings in A2:An

Have your Letters in B1:E1
B1: L
C1: D
D1: O
E1 YM

B2: =REGEX.MID($A2,"(?<="&B$1&"\()\d+")

Copy/drag across to E2

Select B2:E2 and copy/drag down as far as required.
--ron

 

[Ron Rosenfeld]

Using the example in cell A1: L(117),D(93),O(5)

I should have mentioned that Column B should only extract numbers associated
with L. So for A1, Column B1 will show 117.

Column C should only extract numbers associated with D. So for A1, Column
C1 will show 93.

Column D should only extract numbers associated with O. so for A1, Column
D1 will show 5.

Using the example from cell A3. GRTR1%orYM(119),O(3)
Column E should only extract numbers associated with YM. So for A3, Column
E1 will show 119.

The length of my numbers varies.

Any help is much appreciated

Again, with your strings A2:An and your Identifiers in B1:E1, you could use the
following formula:

=IF(OR(B$1="",ISERR(FIND(B$1&"(",$A2))),"",
MID($A2,FIND(B$1&"(",$A2)+LEN(B$1)+1,
FIND(")",$A2,FIND(B$1&"(",$A2)+LEN(B$1)+1)-
(FIND(B$1&"(",$A2)+LEN(B$1)+1)))

It is more complicated than the regular expression formula, but does not
require downloading any add-ins.
--ron

 

[Guest]

Ron, the formula below works great and so does the add-in.
I came across another string that i would like to extract.
L(26),GRTR1%orYM(26),D(6),O(5).

How would I extract the 1% or any % from the string above? The % always
come after GRTR.

 

[Ron Rosenfeld]

Ron, the formula below works great and so does the add-in.
I came across another string that i would like to extract.
L(26),GRTR1%orYM(26),D(6),O(5).

Since you've downloaded and installed the add-in, I will use the simpler, regex
formula:

=REGEX.MID(A2,"\d+(?=%)")

will extract the digits prior to the % sign. It will not extract the % sign.

Again, as in the former, this formula assumes that the value is a positive
integer. The regex can be modified if that is not always the case.

Let me point out also that the formulas return the numeric value as TEXT. So
there may be some functions that will treat these values as TEXT and not as
numbers.

If that is an issue, you should precede the functions that extract the values
with a double unary, to change it from Text to Number.

e.g.

=--REGEX.MID(A2,"\d+(?=%)")

If, for example though, you wanted to extract 0.01 (i.e. the value of 1%), you
could do this:

=--REGEX.MID(A2,"\d+%")

That extracts the 1 followed by the "%". The double unary then changes "1%"
into it's actual value of 0.01
--ron

 

[Guest]

Ron, Thanks for all your help. Would it be possible to have this in a
formula as well? I'm always working on different computers so downloading
the add-in each time may be too time consuming. Again, you have been a great
help!

 

[Ron Rosenfeld]

Ron, Thanks for all your help. Would it be possible to have this in a
formula as well? I'm always working on different computers so downloading
the add-in each time may be too time consuming. Again, you have been a great
help!

I cannot this evening.

However, if you installed the add-in using the default options, you should be
able to embed the add-in in the workbook. Therefore, when you distribute the
workbook to different computers, the add-in will "come along" and there will be
no need to re-install it.

Look under the Tools menu for a "morefund" option.

Tools/Morefunc/Embed Morefunc in the workbook.
--ron

 

[Guest]

I installed the add-in using the default option but when I opened the file at
a different computerr, an error message popped up saying that the program
can't be found. any suggestions?

 

[Ron Rosenfeld]

I installed the add-in using the default option but when I opened the file at
a different computerr, an error message popped up saying that the program
can't be found. any suggestions?

That's an odd message. I would have expected a NAME error at the functions.
What, exactly, were you doing when you got a "program cannot be found" error?


My understanding of the process:

Open the workbook on the machine where you originally downloaded the add-in,
and everything is working properly.

Select Tools/Morefunc/Embed Morefunc in the Workbook.

Save the file.

Now you should be able to open the workbook on another machine and still have
the functions working.


--ron

 

[Guest]

Ron,

I got the error message when I opened up the file on another computer. So I
went to Tools/Add-ins/More Func and the add-in is in the workbook. Then I
went to the Insert/Function but the "Regex.mid" function is not available.
Any suggestions?

 

[Ron Rosenfeld]

Ron,

I got the error message when I opened up the file on another computer. So I
went to Tools/Add-ins/More Func and the add-in is in the workbook. Then I
went to the Insert/Function but the "Regex.mid" function is not available.
Any suggestions?

On the machine where things are working properly, when you select
Tools/Morefunc/Embed Morefunc, what does the dialog box show?

What version is installed?
Which of the options are checked?
--ron