substitution: a better method?
Sloth,
Thanks for the reply, but "x" was used as a placeholder for any non-numeric
characters.
--
tj
"Sloth" wrote:
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
"tjtjjtjt" wrote:
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
|