Thanks, Bernard.


--
tj


"Bernard Liengme" wrote:

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

"tjtjjtjt" wrote in message
...
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.

--
tj