Extract numbers from a range of cells

[austenr]

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]

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]

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]

hmmm..That is not what I was looking for. See the attached example file.

[austenr]

xld. Not sure can you apply it to the sample workbook? Thanks

[Bob Phillips]

.

[MWE]

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]

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]

When I copy xld's formulas to other cells I get #ref!. Thought I could just copy down as paste, special, formulas.

[MWE]

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:

1. the numeric portion consists of 10 digits (A) followed by 9 digits (B)
2. the alpha portion (C) is of variable length
3. 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]

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]

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]

It is late, I did not see it. Thanks. Works great.

[MWE]

It is late, I did not see it. Thanks. Works great.

glad I could help

[Bob Phillips]

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]

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]

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]

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]

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]

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.