[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel macro to extract text from a cell

Posted on 2006-06-07
8
Medium Priority
?
570 Views
Last Modified: 2008-01-09
Hello Experts! Can someone help me extract some text from a cell i VB?!

For instance, I have a cell with this sort of content (ignore the quotation marks): "ALC: IECP SP06 C (67)" or it can contain something like: "ALC: AIEP (266)", or even this "ALC: Agent Certs (6)"
Basically, the cell data's lenght changes, however, what i am interested in is the NUMBER part of any of the above examples. I would like to be able to extract the number value and assign it to a variable to i can later add these values up. For instance, how can i extract 67 from a cell that contains this text: "ALC: IECP SP06 C (67)" or 266 from a cell with this data in it: "ALC: AIEP (266)" etc? Do i need to use Regular Expression? Again, the cells length differs, only numbers are consistently surrounded by two brackets (). So, the numebr i need extracted and assigned to variable is always surrounded by ( and ).

Thanks a lot for any help!!!

-=G=-
0
Comment
Question by:Gvigorus
  • 5
  • 3
8 Comments
 
LVL 35

Expert Comment

by:mvidas
ID: 16855018
Hi G,

You could use regular expressions for this, and it certainly would work well, but you don't need them:

 Dim iPos As Long
 iPos = InStr(1, CellVariable.Text, "(")
 If InStr(iPos, CellVariable.Text, ")") > 0 Then
  MsgBox Mid(CellVariable.Text, iPos + 1, InStr(iPos, CellVariable.Text, ")") - iPos - 1)
 End If

Matt
0
 

Author Comment

by:Gvigorus
ID: 16855360
Looks nice! You are looking for position of the first ( and if ( actually there, then you return characters with MID functions that are between ( and ). That's really nice. I will have to figure out how to impliment this in my worksheet, but it makes sense. I'll let you know how this works. Thanks
-=G=-
0
 
LVL 35

Expert Comment

by:mvidas
ID: 16855388
Sounds good, let me know if you need help integrating the idea into your current code
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:mvidas
ID: 16855437
If you did want to extract the number using regexp, you can always have a function for it:

Function ExtractNumber(ByVal CLL As Range) As Long
 Dim RegEx As Object
 Set RegEx = CreateObject("vbscript.regexp")
 RegEx.Pattern = "\((\d+)\)"
 If RegEx.Test(CLL.Text) Then
  ExtractNumber = CLng(RegEx.Execute(CLL.Text).Item(0).SubMatches(0))
 End If
 Set RegEx = Nothing
End Function

If you'll be using this in a macro you'd be better off including this in your sub, and just using the "If RegEx.Test..." block on the cell you're looping through. But in any case, though I'd give that to you too.
Matt
0
 

Author Comment

by:Gvigorus
ID: 16855723
I think there's a little logical error though. I was just taking this string for an example:

CellVar = ALC: IECP SP06 C (67)

iPos = InStr(1, CellVar, "(")                             'This returns 18 as location of "("
IfInStr(iPos, CellVar, ")") > 0 Then                   'which ")" will be graeter the 0, in this example it's 21
MsgBox Mid(CellVar, iPos + 1, InStr(iPos, CellVar, ")") - iPos - 1)  'in this function we are looking at CellVar, adding 1 to 18 = 19, then looking at CellVar again from position 19 where the numbers start, to find out position of ")". The position of ")" would be found as 4, because it's 4 positions away from iPos, which is 18. So, then we subtract iPos, which is 4 - 18 = 14 minus 1 is -15

MID function won't know what -15 means i don't think. Is my thinking correct or am i missing something? Sorry, i'm a newb, but i want to learn!

Thanks! -=G=-
0
 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
ID: 16855804
CellVar = ALC: IECP SP06 C (67)

iPos = InStr(1, CellVar, "(")                             'This returns 18 as location of "("
IfInStr(iPos, CellVar, ")") > 0 Then                   'which ")" will be graeter the 0, in this example it's 21
'you are correct up to this point
MsgBox Mid(CellVar, iPos + 1, InStr(iPos, CellVar, ")") - iPos - 1)'in this function we are looking at CellVar, adding 1 to 18 = 19, then looking at CellVar again from position 19 where the numbers start, to find out position of ")". The position of ")" would be found as 4, because it's 4 positions away from iPos, which is 18. So, then we subtract iPos, which is 4 - 18 = 14 minus 1 is -15

If you look at the return of     InStr(iPos, CellVar, ")")      (which you are in the time before), it won't return "4", it will still return 21, since it is looking for the position of ) starting at position 18.  If iPos is 18, and the InStr function here is 21, then
InStr(iPos, CellVar, ")") - iPos - 1
is the same as
21 - 18 - 1 = 2
So the mid statement will be Mid(CellVar, 19, 2), which returns the 67.  

If instead of using
 InStr(iPos, CellVar, ")")
I had used
 InStr(iPos, Mid(CellVar, iPos), ")")
Then you would be correct in thinking it will return 4.  Easy mistake to make, as the logic follows your train of thought, however VB does not in this case follow that :)

Matt
0
 

Author Comment

by:Gvigorus
ID: 16855975
Tested the InStr method. It worked awesome! Thanks a lot for giving me the RegEx method as well! I am kind of affraid of Regular Expressions, they are hard. InStr Method is great though.

-=G=-
0
 
LVL 35

Expert Comment

by:mvidas
ID: 16856005
RegExp is definately tricky, and takes getting used to, but can be very powerful in parsing strings.  In this case it was very much overkill, but an example couldn't hurt.  I'm happy to help you learn if you should have any more questions!
Matt
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month19 days, 14 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question