substitution: a better method?

[Guest]

I had a column of about 41,000 values. I needed to extract any set of
consecutive integers into separate cells. There could be any number of
letters or symbols between numbers. I did not figure out a way to do this
with the Substitute function.

Is there a method for converting any non-numeric character in a cell into a
single character? I mean, other than Find / Replace?

Examples:
In column Desired results
1234xx789 1234 | 789
xx345xx890 345 | 890
1234567890 1234567890
12x45x78xx 12 | 24 | 78

I've seen a UDF that would strip out all of the non-numeric characters and
leave a number, but I need to treat each consecutive set of integers as a
separate number.

 

[Bernard Liengme]

When you use =Fixer(a1), this UDF will return 123|456 when A1 has 123xxx456
where xxx is any string of non-digits

Function fixer(mycell)
mytest = 0
mystring = ""
mycell = UCase(mycell)
For j = 1 To Len(mycell)
myletter = Mid(mycell, j, 1)
If Asc(myletter) >= 48 And Asc(myletter) <= 57 Then
mystring = mystring & myletter
Else
If mytest = 0 Then
mystring = mystring & "|"
mytest = mytest + 1
End If
End If
Next j
fixer = mystring
End Function

You can then use Copy | Paste Special to convert the formula to its
displayed value. Then use Text to Column to get separate numbers into
separate cells.
I suppose a subroutine would be better but I am short of time.
best wishes

 

[Guest]

Select the cells in your column and run:

Sub tj()
For Each r In Selection
v = r.Value
l = Len(v)
chold = "A"
builup = ""
For i = 1 To l
ch = Mid(v, i, 1)
If IsNumeric(ch) Then
buildup = buildup & ch
chold = ch
Else
If IsNumeric(chold) Then
buildup = buildup & "A"
chold = "A"
End If
End If
Next

If Right(buildup, 1) = "A" Then
buildup = Left(buildup, Len(buildup) - 1)
End If

s = Split(buildup, "A")

For i = 0 To UBound(s)
r.Offset(0, i + 1).Value = s(i)
Next
Next
End Sub

 

[Guest]

highlight the column
click Data->Text to Columns
select Delimited and click next
select Other and put an x in the box, select "Treat consecutive delimiters
as one", and click Finish

the only downside is that xx345xx890 will result in a blank cell like this
| 345 | 890

if you want to delete the blanks...
click Edit->Go To
click special
select blanks, and click OK
click edit -> delete
select shift cells left, and click OK

 

[Guest]

Thanks, Gary. I changed builup = "" to buildup = "", and it works for all the
combinations in my spreadsheet.

 

[Guest]

Sloth,

Thanks for the reply, but "x" was used as a placeholder for any non-numeric
characters.

 

[Guest]

Thanks, Bernard.


--
tj

When you use =Fixer(a1), this UDF will return 123|456 when A1 has 123xxx456
where xxx is any string of non-digits

Function fixer(mycell)
mytest = 0
mystring = ""
mycell = UCase(mycell)
For j = 1 To Len(mycell)
myletter = Mid(mycell, j, 1)
If Asc(myletter) >= 48 And Asc(myletter) <= 57 Then
mystring = mystring & myletter
Else
If mytest = 0 Then
mystring = mystring & "|"
mytest = mytest + 1
End If
End If
Next j
fixer = mystring
End Function

You can then use Copy | Paste Special to convert the formula to its
displayed value. Then use Text to Column to get separate numbers into
separate cells.
I suppose a subroutine would be better but I am short of time.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

 

[Ron Rosenfeld]

I had a column of about 41,000 values. I needed to extract any set of
consecutive integers into separate cells. There could be any number of
letters or symbols between numbers. I did not figure out a way to do this
with the Substitute function.

Is there a method for converting any non-numeric character in a cell into a
single character? I mean, other than Find / Replace?

Examples:
In column Desired results
1234xx789 1234 | 789
xx345xx890 345 | 890
1234567890 1234567890
12x45x78xx 12 | 24 | 78

I've seen a UDF that would strip out all of the non-numeric characters and
leave a number, but I need to treat each consecutive set of integers as a
separate number.

This macro should do what you describe. Select the range you wish to parse;
the integer groups will be placed into adjacent columns.

Depending on the setup of your worksheet, you may want to clear out adjacent
columns first; or insert new ones.

================================Option Explicit
Sub ParseIntegers()
Dim c As Range
Dim re As Object
Dim mc As Object
Const sPat As String = "\d+"
Dim i As Long

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

For Each c In Selection
If re.test(c.Text) = True Then
Set mc = re.Execute(c.Text)
For i = 1 To mc.Count
c.Offset(0, i).Value = mc(i - 1)
Next i
End If
Next c

End Sub
====================================


--ron

 

[Guest]

Ron,

I haven't had an opportunity to test this, but thank you for the reply.