How do I split a column having numbers and text in a random order
I want to split the following column to two columns of numbers and text:
Sf741 Tn Ny11Sa C 2 3Ty456 56Rd345 123Tyre Byety6 784 56T7 |
Hi
as a starting point: http://www.dicks-blog.com/archives/2...t-1/trackback/ -- Regards Frank Kabel Frankfurt, Germany "rana8689" schrieb im Newsbeitrag ... I want to split the following column to two columns of numbers and text: Sf741 Tn Ny11Sa C 2 3Ty456 56Rd345 123Tyre Byety6 784 56T7 |
rana
Copy the column twice then run each of these on separate columns. Sub RemoveAlphas() '' Remove alpha characters from a string. Dim intI As Integer Dim rngR As Range, rngRR As Range Dim strNotNum As String, strTemp As String Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues) For Each rngR In rngRR strTemp = "" For intI = 1 To Len(rngR.Value) If Mid(rngR.Value, intI, 1) Like "[0-9]" Then strNotNum = Mid(rngR.Value, intI, 1) Else: strNotNum = "" End If strTemp = strTemp & strNotNum Next intI rngR.Value = strTemp Next rngR End Sub Sub RemoveNums() '' Remove numeric characters from a string. Dim intI As Integer Dim rngR As Range, rngRR As Range Dim strNotNum As String, strTemp As String Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues) For Each rngR In rngRR strTemp = "" For intI = 1 To Len(rngR.Value) If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then strNotNum = Mid(rngR.Value, intI, 1) Else: strNotNum = "" End If strTemp = strTemp & strNotNum Next intI rngR.Value = strTemp Next rngR End Sub Your column with Alphas will have spaces. To strip these out....... Public Sub Strip_WhiteSpace() Selection.Replace What:=" ", _ Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub Gord Dibben Excel MVP On Fri, 17 Dec 2004 06:37:15 -0800, "rana8689" wrote: I want to split the following column to two columns of numbers and text: Sf741 Tn Ny11Sa C 2 3Ty456 56Rd345 123Tyre Byety6 784 56T7 |
All times are GMT +1. The time now is 10:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com