PDA

View Full Version : [SOLVED] Extract numbers from a range of cells



austenr
09-01-2005, 01:41 PM
Good Day,

I have the need to extract the nine digit number immediatly in front of the first alpha character in a cell.

for example my number might be:

5049740520111111111BRITAIN

I also need a way to loop through and extract the number from the above string and take that number and put it in a cell of its own along with the remaining data in cells of their own. The completed entry should be in a1 - c 1

5049740520 111111111 BRITAIN

I know how to take the number off of the front and back but have never taken out the middle ones before. Any help would be appreciated. Thanks

MWE
09-01-2005, 02:42 PM
Good Day,

I have the need to extract the nine digit number immediatly in front of the first alpha character in a cell.

for example my number might be:

5049740520111111111BRITAIN

I also need a way to loop through and extract the number from the above string and take that number and put it in a cell of its own along with the remaining data in cells of their own. The completed entry should be in a1 - c 1

5049740520 111111111 BRITAIN

I know how to take the number off of the front and back but have never taken out the middle ones before. Any help would be appreciated. Thanks
First, striping off the numerical portion: my approach would be to stuff the target text into a string array, index along that array using the mid function to pull out individual characters, checking each character for "non numeric" and then strip off the first part. Since the "number portion" could be of any size, I would probably keep things as string. Something like:

Sub StripNums()
Dim I As Long
Dim NumPortion as string
Dim strThing As String
strThing = "5049740520111111111BRITAIN"
For I = 1 To Len(strThing)
If IsNumeric(Mid(strThing, I, 1)) <> True Then
NumPortion = Left(strThing, I - 1)
MsgBox NumPortion
Exit Sub
End If
Next I
End Sub

should work

For the second part, it is not very clear exactly what you want to do. Assuming you want to stuff NumPortion into a cell and the balance of the text into a 3rd cell, you might consider:


Sub StripNums()
Dim I As Long
Dim strThing As String
strThing = Cells(1, 1)
For I = 1 To Len(strThing)
If IsNumeric(Mid(strThing, I, 1)) <> True Then
MsgBox Left(strThing, I - 1)
Cells(1, 2) = Left(strThing, I - 1)
Cells(1, 3) = Right(strThing, Len(strThing) - I + 1)
Exit Sub
End If
Next I
End Sub

Bob Phillips
09-01-2005, 04:34 PM
austenr,

First some assumptions.

I have assumed that the data is in A1, and that there will always be at leats 9 digitd bfollowed by som text, and the digits come first.

In 3 cells add these 3 formula to get the three components


=LEFT($A2,MIN(IF(ISERROR(1*(MID($A2,ROW(INDIRECT("A"&ROW()&":A"&LEN($A2))),1))),ROW(INDIRECT("A"&ROW()&":A"&LEN($A2))),255))-1-9)

=MID($A2,MIN(IF(ISERROR(1*(MID($A2,ROW(INDIRECT("A"&ROW()&":A"&LEN($A2))),1))),ROW(INDIRECT("A"&ROW()&":A"&LEN($A2))),255))-9,9)

=RIGHT($A2,LEN($A2)-MIN(IF(ISERROR(1*(MID($A2,ROW(INDIRECT("A"&ROW()&":A"&LEN($A2))),1))),ROW(INDIRECT("A"&ROW()&":A"&LEN($A2))),255))+1)

all three are array formulae, so commit with Ctrl-Shift-Enter

austenr
09-01-2005, 04:42 PM
hmmm..That is not what I was looking for. See the attached example file.

austenr
09-01-2005, 04:50 PM
xld. Not sure can you apply it to the sample workbook? Thanks

Bob Phillips
09-01-2005, 05:00 PM
.

MWE
09-01-2005, 05:19 PM
hmmm..That is not what I was looking for. See the attached example file.
your "before" and "after" makes it pretty obvious what you want. However, what is the RULE that is used to split the numerical portion? a certain number of digits? transition from any # other than "1" to "1"? something else?

austenr
09-01-2005, 05:31 PM
The format will always be the same except for the name which is inconsequential in length. The numbers are set and will not change position.

austenr
09-01-2005, 05:46 PM
http://vbaexpress.com/forum/images/smilies/102.gifWhen I copy xld's formulas to other cells I get #ref!. Thought I could just copy down as paste, special, formulas. http://vbaexpress.com/forum/images/smilies/banghead.gif

MWE
09-01-2005, 05:46 PM
The format will always be the same except for the name which is inconsequential in length. The numbers are set and will not change position.
I assume:


the numeric portion consists of 10 digits (A) followed by 9 digits (B)
the alpha portion (C) is of variable length
you wish to read what is in cell A1, pull it apart and put (A) into A1, (B) into B1 and (C) into C1

This is a much easier problem and does not require string scanning. If you were willing to leave the original data in A1 and put (A), (B) and (C) somewhere else (like B1, C2, D1), then you would not need VBA

VBA code to do what you want:


Sub StripNums()
Dim strThing As String
strThing = Cells(1, 1)
Cells(1, 1) = Left(strThing, 10)
Cells(1, 2) = Mid(strThing, 11, 9)
Cells(1, 3) = Right(strThing, Len(strThing) - 19)
End Sub

austenr
09-01-2005, 06:45 PM
Great That works much better. Having trouble looping through the whole file row by row. Here is my code:


Sub StripNums()
Dim strThing As String
FinalRow = Cells(65536, 1).End(xlUp).Row
For I = 2 To FinalRow
strThing = Cells(1, 1)
Cells(1, 1) = Left(strThing, 10)
Cells(1, 2) = Mid(strThing, 11, 9)
Cells(1, 3) = Right(strThing, Len(strThing) - 19)
Next I
End Sub



Getting invalid call or argument

MWE
09-01-2005, 07:17 PM
Great That works much better. Having trouble looping through the whole file row by row. Here is my code:


Sub StripNums()
Dim strThing As String
FinalRow = Cells(65536, 1).End(xlUp).Row
For I = 2 To FinalRow
strThing = Cells(1, 1)
Cells(1, 1) = Left(strThing, 10)
Cells(1, 2) = Mid(strThing, 11, 9)
Cells(1, 3) = Right(strThing, Len(strThing) - 19)
Next I
End Sub

Getting invalid call or argument

to loop through the rows, you need to use the loop index I in each row-based statement within the loop:


Sub StripNums()
Dim FinalRow As Long
Dim I As Long
Dim strThing As String
FinalRow = Cells(65536, 1).End(xlUp).Row
For I = 2 To FinalRow
strThing = ActiveSheet.Cells(I, 1).Text
Cells(I, 1) = "abc" 'Left(strThing, 10)
Cells(I, 2) = Mid(strThing, 11, 9)
Cells(I, 3) = Right(strThing, Len(strThing) - 19)
Next I
End Sub


this code works fine for me (Excel2000 under WinXP/SP2); not sure why you are getting an error

austenr
09-01-2005, 07:26 PM
http://vbaexpress.com/forum/images/smilies/banghead.gifhttp://vbaexpress.com/forum/images/smilies/banghead.gifhttp://vbaexpress.com/forum/images/smilies/banghead.gif It is late, I did not see it. Thanks. Works great.

MWE
09-01-2005, 07:35 PM
http://vbaexpress.com/forum/images/smilies/banghead.gifhttp://vbaexpress.com/forum/images/smilies/banghead.gifhttp://vbaexpress.com/forum/images/smilies/banghead.gif It is late, I did not see it. Thanks. Works great.
glad I could help

Bob Phillips
09-02-2005, 12:54 AM
Someday someone will have to explain to me what the attraction is in building VBA to do things that Excel can do quite happily.

I copied the formula down as shown in the attached, absolutely no problem. The formula has the advantage no VBA, it doesn't care about any lengths apart from the 9 digits that it extracts from the middle, and so is re-uasable.

MWE
09-02-2005, 08:04 AM
Someday someone will have to explain to me what the attraction is in building VBA to do things that Excel can do quite happily.

I copied the formula down as shown in the attached, absolutely no problem. The formula has the advantage no VBA, it doesn't care about any lengths apart from the 9 digits that it extracts from the middle, and so is re-uasable.
The attraction to me is the portability to any appl that supports VB/VBA. I fiddle with several MS appls (Access, Excel, PowerPoint, Project and Word) as well as custom appls. The ability to develop and reuse code is important. For problems focused on, say, Excel then using built in capability as much as possible is appropriate and more efficient. It is also true that the purpose/nature of this forum and its participants are somewhat biased to VBA solutions.

You have picked a poor example because your formulas do not solve the problem as posed. In particular Austenr requested that the data be separated into 3 pieces and the first piece be stuffed back into the original cell. That is a real challenge for Excel's normal capability.

Regarding the reusability statement, I suggest that VBA code could have been written to be just as reusable. I chose not to because Austenr stated that the format of the numeric portion never changed and it seemed more appropriate to provide him with "simple" code.

austenr
09-02-2005, 08:08 AM
I don't want to get in the middle of this but the code that MWE provided did solve the problem in a way I can understand more easliy. And I had a situation today that I was able to modify MWE's code a little that worked perfectly. I am not saying either is the best way but sometimes the formulas, especially long ones, confuse me. Thank you both for your suggestions and contributions. They were a great help.

MWE
09-02-2005, 08:18 AM
I don't want to get in the middle of this but the code that MWE provided did solve the problem in a way I can understand more easliy. And I had a situation today that I was able to modify MWE's code a little that worked perfectly. I am not saying either is the best way but sometimes the formulas, especially long ones, confuse me. Thank you both for your suggestions and contributions. They were a great help.

I had not thought of this, but Austenr brings up a very valid point about confusion. I have looked at some of the formulas supplied to solve certain problems and was amazed at their complexity (and a little awed at the author's ability to create it). Building them "one thought at a time" is quite different from looking at the final product and understanding what it does, how it does it, and how to modify it to do something else. Those of you who have looked at my code (and not puked) will notice that I tend to do "one thing at a time" so it is easy to read, understand and modify.

I recollect a statement somewhere in J-Walks book on programming with VBA in Excel about "formula confusion" and how that can often be eliminated with VBA code (hopefully I have not misrepresented John's intent)

austenr
09-02-2005, 08:35 AM
I think if you answer someones question and are not sure of his or her capabilities to understand something, you should not assume they do. When dealing with someone who I do not know their abilities on nay topic, I will always make the assumption that their knowledge is limited if it is or not, and answer the question in the simplest method I can. I do this because no matter their level, they are more likely to understand it. If you make the answer too complex several things might happen. They might discouraged and not even attempt to utilize your help or they might have to keep asking you how to do the same thing you thought you provided an acceptable answer to start with. Again this is not directed to anyone in particular just my opinion.

Everyone on this board that answers questions do an excellent job and have patients with those that are not as gifted as some to figure things out easily. For all of that I am greatful.

Bob Phillips
09-02-2005, 09:46 AM
The attraction to me is the portability to any appl that supports VB/VBA. I fiddle with several MS appls (Access, Excel, PowerPoint, Project and Word) as well as custom appls. The ability to develop and reuse code is important. For problems focused on, say, Excel then using built in capability as much as possible is appropriate and more efficient.

Perhaps you can enlighten me as to what part of this code is re-usable within Powerpoint, Project or Word.


Cells(1, 1) = Left(strThing, 10)

In reality, very little VBA code developed in Excel has any re-usability in other Excel programs let alone other apps (unless you are in to classes of course, which means you think in a different paradigm), because the developer will naturally be working with the Excel object, and taking advantage of the facilities offered.


It is also true that the purpose/nature of this forum and its participants are somewhat biased to VBA solutions.

This of course is true, and what originally drew me here, but I often make the point that we should never be in rush to VBA, don't forget some sites ban VBA.


You have picked a poor example because your formulas do not solve the problem as posed. In particular Austenr requested that the data be separated into 3 pieces and the first piece be stuffed back into the original cell. That is a real challenge for Excel's normal capability.

I think you are being disingenuous there. This is what austenr actually said in the first posting, and I quote ...


I also need a way to loop through and extract the number from the above string and take that number and put it in a cell of its own along with the remaining data in cells of their own

and his example that he posted was in three distinctly separate cells.

austenr
09-02-2005, 10:21 AM
I wanted to say that both of you solved my problem satisfactorly, however, in this case MWE's solution worked best for me simply because his was easier for me to understand. I appreciate both of your efforts. I am sure I have a lot less knowledge than either of you and the simpler the better for me. Xld, some of your solutions in the past I were able to follow and appreciate very much, but in this case I really could not follow it to where it made sense for me.

Bob Phillips
09-02-2005, 10:54 AM
I wanted to say that both of you solved my problem satisfactorly, however, in this case MWE's solution worked best for me simply because his was easier for me to understand. I appreciate both of your efforts. I am sure I have a lot less knowledge than either of you and the simpler the better for me. Xld, some of your solutions in the past I were able to follow and appreciate very much, but in this case I really could not follow it to where it made sense for me.

No that is fine auster, you are the customer, it is your prerogative. And if MWE gave you a solution you can learn from, so much to the good.

But I also reserve the right to disagree :devil:, and to argue the case for not rushing into VBA, which I really believe in, but too often lapse myself .

And I do admit, my solution in this case is not simple, but it will be explained in my book http://vbaexpress.com/forum/images/smilies/001.gif.

MWE
09-02-2005, 11:15 AM
No that is fine auster, you are the customer, it is your prerogative. And if MWE gave you a solution you can learn from, so much to the good.

But I also reserve the right to disagree :devil:, and to argue the case for not rushing into VBA, which I really believe in, but too often lapse myself .

And I do admit, my solution in this case is not simple, but it will be explained in my book http://vbaexpress.com/forum/images/smilies/001.gif.
xld: I have followed your posting career (you must have the record for # posts/minute) with genuine interest. You certainly have an excellent grasp of Excel (vanilla or VBA enhanced) and how to use it effectively:thumb. I have learned a lot from your posts (and most experiences were positive:devil:).

I look forward to your book. Do you have any publication particulars?

johnske
09-02-2005, 02:15 PM
Someday someone will have to explain to me what the attraction is in building VBA to do things that Excel can do quite happily.... Sure Bob,

I have a very real aversion to worksheet solutions, IMHO I think they're fine for "quicky' solutions, small jobs, or building a model, but that VBA should be the ultimate 'end goal' for serious projects...

My 1st large project with Excel was an archery database for my club members & I thort I'd try a s/s solution (that, of necessity, included lotsa links {used for sorting and collating "records"}) and very little VBA. It turned out to be (with help files etc) around 300 MB!!! :p A monster!!

For all that much memory, it was only good for 50 workbooks for 50 ppl and about 10 years of records, was very slow, only had limited (partial) functionality and there were problems such as having to copy & transfer records from a smaller to a larger pre-coded workbook 'template' for anyone that stuck around in the sport too long. :doh:

After so much work getting it all sorted out and working properly I was very disappointed but eventually abandoned that idea altogether & this led to my 1st large VBA project that used the first as a basic model to work from.

The final VBA version involves a master workbook (a 'control panel') with ~1 MB of VBA code and about 6 lines of worksheet code, the other 20 workbooks used as invidual databases currently have 5 years records for 20 ppl and the whole lot (with help files etc) only occupies 3.6MB - if records ever get to 64MB RAM, a rough estimate is that it'd then hold ~1000 ppls records for the next (conservatively) 300 years :rotlaugh: or so. It also has many more functions and is very fast for what has to be done.

The essential difference is that instead of having to use 20 (or more) pre-coded workbooks with links, I now have 20 (or more) workbooks with data but no code or links at all in them, and one workbook that contains all the code - but no data

So, VBA wherever possible for me from now on... :devil:

(n.b. I started off with, but didn't want to use Access for this project cos it only had limited formatting options for presentation)

Regards,
John :thumb

MWE
09-02-2005, 02:31 PM
Sure Bob,

I have a very real aversion to worksheet solutions, IMHO I think they're fine for "quicky' solutions, small jobs, or building a model, but that VBA should be the ultimate 'end goal' for serious projects...

My 1st large project with Excel was an archery database for my club members & I thort I'd try a s/s solution (that, of necessity, included lotsa links {used for sorting and collating "records"}) and very little VBA. It turned out to be (with help files etc) around 300 MB!!! :p

For all that memory, it was only good for 50 workbooks for 50 ppl and about 10 years of records, was very slow, only had limited (partial) functionality and there were problems such as having to copy & transfer records from a smaller to a larger pre-coded workbook 'template' for anyone that stuck around in the sport too long. :doh:

After so much work getting it all sorted out and working properly I was very disappointed but eventually abandoned that idea altogether & this led to my 1st large VBA project that used the first as a basic model to work from.

The final VBA version involves a master workbook (a 'control panel') with ~1 MB of VBA code and about 6 lines of worksheet code, the other 20 workbooks used as invidual databases currently have 5 years records for 20 ppl and the whole lot (with help files etc) only occupies 3.6MB - if records ever get to 64MB RAM, a rough estimate is that it'd then hold ~1000 ppls records for the next (conservatively) 300 years :rotlaugh: or so. It also has many more functions and is very fast for what has to be done.

The essential difference is that instead of having to use 20 (or more) pre-coded workbooks with links, I now have 20 (or more) workbooks with data but no code or links at all in them, and one workbook that contains all the code - but no data

So, VBA wherever possible for me from now on... :devil:

(n.b. I started off with, but didn't want to use Access for this project cos it only had limited formatting options for presentation)

Regards,
John :thumb
I am probably putting my foot in it, but ...

I absolutely agree with your points about large, complex applications. The point about separation of code and data is very important and often lost in today's environment. But I think xld's comment was really about smaller, simpler problems that "...Excel can do quite happliy."

I think there are cases where VBA is the obvious way to go and cases where basic Excel is obvious. Perhaps the real question is about those gray areas, what choices we make and why. Personally, I will always opt for clarity and ease of understanding and do not like complicated spreadsheets and formulas. There are just too many opportunites for things to go wrong. But I know many people who think VBA/macros are evil and should not be used if there is ANY POSSIBLE WAY to avoid them. I have shown people how to eliminate hundreds of formulas and extra cells (for intermediate calcs) with a 10 line macro. They were not interested.

Bob Phillips
09-02-2005, 04:25 PM
I absolutely agree with your points about large, complex applications. The point about separation of code and data is very important and often lost in today's environment. But I think xld's comment was really about smaller, simpler problems that "...Excel can do quite happliy."

I think I would agree with that too. In my experience, the best apps are either VB executables that interact with Excel and produce an Excel output, or a mixture of VB code with appropriate use of built-in functionality.

Separation of code and data is a crucial point too. I would even go so far as to say that in every app, data, logic and presentation should be separated, physically if possible, logically at the very least.

Whilst we all know that some things cannot be done without VBA, more than 3 conditional formats as an example, I cannot endorse johnske's statement of


I have a very real aversion to worksheet solutions, IMHO I think they're fine for "quicky' solutions, small jobs, or building a model, but that VBA should be the ultimate 'end goal' for serious projects...

as that is too cut-and dried. Beware abolutes is my motto, or one of them, and I believe strongly in using the built-in functionality where possible, whilst of course ensuring. If you don't beleive in using Excel's functionality, why use Excel?

Zack Barresse
09-02-2005, 05:04 PM
I agree with xld here on Excel's native funtionality. If it's there - use it. We can't match it with VBA.

Bob Phillips
09-03-2005, 01:55 AM
I agree with xld here on Excel's native funtionality. If it's there - use it. We can't match it with VBA.

The other thing I should have said is that from johnske's description, he chose the wrong tool for his app, it is clearly better suited as a database app, SQL server and VB, or at worst Access, but not Excel. He could have used Excel as to analyse summarised data.

johnske
09-03-2005, 02:53 AM
:rofl: I just KNEW I was gonna get a couple of strong bites :rofl: ...

Why use Excel? Well can I say it wasn't very well suited to Word, Powerpoint or Outlook, I suppose I could've used C or VB but why not just use a workbook?

And why use XL VBA instead of "database app, SQL server and VB, or at worst Access"?... I don't like pigeon-holing a program into "a database app" simply because a database is a small part of it. The major part of it is in ease of use, presentation, sorting, and analysis.

I suppose one good reason is because I didn't and still don't have these DB programs (I'd already stated my objections to access) but also because VBA's very easy, reasonably fast, a memory miser and best of all, unlike VB6 and other similar apps, if you have Office installed - it's totally and completely FREE, you don't have to buy it separately!! :thumb

Also, the thing is that it could be done and was reasonably simple to do it in that manner and it works very very well indeed so why the hell not? I'm always up for doing something unconventionally. :devil:

Also, if you read what I said prior to this (1st para), you'll find I did not dismiss s/s coding altogether, and I said later that I also used about six lines of s/s code (simply because I became lazy at that point and that was the easiest way to do the job).

(BTW, prior to this experience I was also extolling the virtues of s/s coding but due to the experience I became unconverted very quickly).

John

Bob Phillips
09-03-2005, 09:48 AM
Bite, bite, bite .... http://vbaexpress.com/forum/images/smilies/001.gif


And why use XL VBA instead of "database app, SQL server and VB, or at worst Access"?... I don't like pigeon-holing a program into "a database app" simply because a database is a small part of it. The major part of it is in ease of use, presentation, sorting, and analysis.

Nor I mate, but I venture that any Excel workbook that has as much data as yours is using the wrong tool. It is very easy to capture and maintain the data in a database app and use ADo tgo retieve the relevant data within Excel and use Excel for what is may be good at (and I don't mean reports!).


I suppose one good reason is because I didn't and still don't have these DB programs (I'd already stated my objections to access)

Whilst no fan of Access myself, it serves as a good enough data base in a few-user environment IMO. What do you have against it that stops you using a program that you own?


VBA's very easy, reasonably fast, a memory miser and best of all, unlike VB6 and other similar apps, if you have Office installed - it's totally and completely FREE, you don't have to buy it separately!!

I think I would take issue with you on every statement there except that it is freehttp://vbaexpress.com/forum/images/smilies/devil.gif. It is not easy compared to VB, try developing forms in VB6 it is a joy compared to VB6, it is painfully slow compared to VB6, and can gobble memory with the best of them, and can have probelms releasing objects.

.

Bob Phillips
09-03-2005, 09:51 AM
xld: I have followed your posting career (you must have the record for # posts/minute)

I don't even come close to Moose!


I look forward to your book. Do you have any publication particulars?

Not as yet, still finalising the deal.

.