7 {{ upvoteCount | shortNum }}
7 {{ upvoteCount | shortNum }}

Excel Parse Num/text

by baronig
4 replies
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
#excel #num or text #parse
  • Profile picture of the author ehicks727
    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.
    {{ DiscussionBoard.errors[632553].message }}
  • Profile picture of the author bs5143
    Banned
    [DELETED]
    {{ DiscussionBoard.errors[638519].message }}
    • Profile picture of the author Chris_L
      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
      {{ DiscussionBoard.errors[641504].message }}
  • Profile picture of the author baronig
    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"
    {{ DiscussionBoard.errors[641758].message }}
  • Profile picture of the author accessmajor
    Excel can do it natively using Data/Text To Columns
    {{ DiscussionBoard.errors[1307807].message }}

Trending Topics