Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
substitution: a better method?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
substitution: a better method?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
substitution: a better method?
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 -- Gary''s Student - gsnu200754 "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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
substitution: a better method?
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
substitution: a better method?
Thanks, Gary. I changed builup = "" to buildup = "", and it works for all the
combinations in my spreadsheet. -- tj "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200754 "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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
substitution: a better method?
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
substitution: a better method?
On Thu, 8 Nov 2007 04:43:01 -0800, 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. 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
substitution: a better method?
Ron,
I haven't had an opportunity to test this, but thank you for the reply. -- tj "Ron Rosenfeld" wrote: On Thu, 8 Nov 2007 04:43:01 -0800, 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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Substitution of words | Excel Discussion (Misc queries) | |||
Substitution | Excel Discussion (Misc queries) | |||
substitution | Excel Discussion (Misc queries) | |||
Substitution | Excel Discussion (Misc queries) | |||
why does ON = Ambiguous in substitution formula? | Excel Worksheet Functions |