armchairandy
Board Regular
- Joined
- Mar 27, 2012
- Messages
- 53
Hi
This is the problem. I have a column with something like the following:
Column B
Line 1 SS 2 d' FS 0 d
Line 2 SS 5 d' FF 6 d
Line 3 FS 0 d
Line 4 FF 0 d' SS 3 d' FS 0 d' FS 0 d' SS 3 d' FF 11 d'
And so on
each line can be and is different.
What I need is all the numbers extracted into separate columns
so the last line would read
C D E F G H
0 3 0 0 3 11
I have some code that I have found on this site (thank you)
Sub ExtractNumbers()
Dim rng As Range 'range to process
Dim arr As Variant 'array
Dim i As Long 'loop index
Dim col As Long 'column offset for output
Set rng = Sheets("Leads").Range("C1")
Do Until rng = ""
'split values into an array
'then loop through the array to determine if numeric
col = 3
arr = Split(rng.Value, " ")
For i = LBound(arr) To UBound(arr)
If IsNumeric(arr(i)) Then
col = col + 1
rng.Offset(, col).Value = arr(i)
End If
Next i
Set rng = rng.Offset(1, 0)
Loop
Set rng = Nothing
End Sub
This gives me exactly what I want but..................................
There are occasions when this code would need to process over 15,000 lines of similar data and the process is taking too long for this approach to be used.
Is there any way that this code could be quicker, or is there a formula I could use in subsequent columns instead - it would not matter if I had many columns of formula, as long as the process is quick.
Many thanks in advance
Andrew
[TABLE="width: 425"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
This is the problem. I have a column with something like the following:
Column B
Line 1 SS 2 d' FS 0 d
Line 2 SS 5 d' FF 6 d
Line 3 FS 0 d
Line 4 FF 0 d' SS 3 d' FS 0 d' FS 0 d' SS 3 d' FF 11 d'
And so on
each line can be and is different.
What I need is all the numbers extracted into separate columns
so the last line would read
C D E F G H
0 3 0 0 3 11
I have some code that I have found on this site (thank you)
Sub ExtractNumbers()
Dim rng As Range 'range to process
Dim arr As Variant 'array
Dim i As Long 'loop index
Dim col As Long 'column offset for output
Set rng = Sheets("Leads").Range("C1")
Do Until rng = ""
'split values into an array
'then loop through the array to determine if numeric
col = 3
arr = Split(rng.Value, " ")
For i = LBound(arr) To UBound(arr)
If IsNumeric(arr(i)) Then
col = col + 1
rng.Offset(, col).Value = arr(i)
End If
Next i
Set rng = rng.Offset(1, 0)
Loop
Set rng = Nothing
End Sub
This gives me exactly what I want but..................................
There are occasions when this code would need to process over 15,000 lines of similar data and the process is taking too long for this approach to be used.
Is there any way that this code could be quicker, or is there a formula I could use in subsequent columns instead - it would not matter if I had many columns of formula, as long as the process is quick.
Many thanks in advance
Andrew
[TABLE="width: 425"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
