Excel Parse Num/text

by 4 replies
7
I have a spreadsheet would like to separate numbers from text.example:

3318NotreDame would come 3318 NotreDame
6530Wilderton #28 would come 6530 Wilderton #28

what formula can do this?thanks
#programming #excel #num or text #parse
  • I'm pretty sure that's a bit more difficult than an excel formula. I parse addresses all day long using custom Java code that I write. How many records do you have? I might be able to do that quickly, and fairly cheaply for you.
  • Banned
    [DELETED]
    • [1] reply
    • The solution is to breakdown the data in to an array, then reassemble only the numeric portion. The process is a little more complicated than the usual excel formulas. Here is a good explanation from the official Microsoft website.

      I have attached a spreadsheet with the formula applied to your sample question.

      Hope this helps.

      Chris
  • this code solved my problem:

    PHP Code:
    Public Function IsNumberlike(y As String) As Boolean
        IsNumberlike     = False
            If Asc(y) >= Asc("0") And Asc(y) <= Asc("9") Then IsNumberlike = True
            If y = "#" Then IsNumberlike = True
    End     Function
     
    Public Function     SpaceIt(x As String) As String
        Dim OnNumber     As Boolean
        Let SpaceIt     = ""
        ' check whether we are starting with a number
        If IsNumberlike(Mid(x, 1, 1)) Then OnNumber = True
        For i = 1 To Len(x)
            If IsNumberlike(Mid(x, i, 1)) Then
                If Not OnNumber Then Let SpaceIt = SpaceIt + " "
                Let SpaceIt = SpaceIt + Mid(x, i, 1)
                OnNumber = True
            Else
                If OnNumber Then Let SpaceIt = SpaceIt + " "
                Let SpaceIt = SpaceIt + Mid(x, i, 1)
                OnNumber = False
            End If
        Next i
    End Function 
    spaceit("42wallaby way#14 syndey") will become
    "42 wallaby way #14 sydney"
  • Excel can do it natively using Data/Text To Columns
Join the discussion

Next Topics on Trending Feed