Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
rana8689
 
Posts: n/a
Default 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


  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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




  #3   Report Post  
Gord Dibben
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert text to numbers gennario Excel Discussion (Misc queries) 6 January 11th 05 12:56 AM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 11:19 PM
Splitting text in one column into two (or more) columns. RickyDee Excel Worksheet Functions 4 December 7th 04 11:03 PM
I enter numbers and they are stored as text burkeville Excel Discussion (Misc queries) 5 December 3rd 04 02:59 AM
How to Replace numbers and text with numbers only? Robert Judge Excel Worksheet Functions 3 November 5th 04 05:36 PM


All times are GMT +1. The time now is 05:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"