Welcome Guest
Log In | Register )
You last visited December 9, 2016, 12:16 pm
All times shown are
Eastern Time (GMT-5:00)

Separating A String of Numbers

Topic closed. 7 replies. Last post 2 years ago by SergeM.

Page 1 of 1
PrintE-mailLink
lotteryfanatic's avatar - try 20this%202.JPG
California
United States
Member #122106
January 24, 2012
924 Posts
Offline
Posted: February 21, 2015, 12:20 pm - IP Logged

Is there anybody that could program a system in excel form that would separate a mathematical string of numbers like this:

5236584432695390201402736

 into this:

 523 236 365 658 etc....

The string could contain as little as 7 digits or as many as 90 digits.


    MrProgrammer's avatar - Lottery-001.jpg

    United States
    Member #161388
    November 28, 2014
    83 Posts
    Offline
    Posted: February 21, 2015, 1:07 pm - IP Logged

    Sure, what version of excel are you using?

    MrProgrammer  Cool

      lotteryfanatic's avatar - try 20this%202.JPG
      California
      United States
      Member #122106
      January 24, 2012
      924 Posts
      Offline
      Posted: February 21, 2015, 1:17 pm - IP Logged

      Sure, what version of excel are you using?

      I'm using 2010. Did you see the message I sent you?


        MrProgrammer's avatar - Lottery-001.jpg

        United States
        Member #161388
        November 28, 2014
        83 Posts
        Offline
        Posted: February 21, 2015, 1:20 pm - IP Logged

        Yes, saw it about 20 minutes ago, then checked out your profile, and saw this post, so asked that question here, so saw both.  2010, ok thanks.  Will have soon, and will answer both places.

        MrProgrammer  Cool

          lotteryfanatic's avatar - try 20this%202.JPG
          California
          United States
          Member #122106
          January 24, 2012
          924 Posts
          Offline
          Posted: February 21, 2015, 1:28 pm - IP Logged

          Yes, saw it about 20 minutes ago, then checked out your profile, and saw this post, so asked that question here, so saw both.  2010, ok thanks.  Will have soon, and will answer both places.

          Thanks a million!!!


            MrProgrammer's avatar - Lottery-001.jpg

            United States
            Member #161388
            November 28, 2014
            83 Posts
            Offline
            Posted: February 21, 2015, 2:31 pm - IP Logged

            Ok, here you go.  Did it first with no programming, but a headache, so better a program function.
             
            In your excel spreadsheet, do a Alt PF11.  Pick Insert > Module from top, a window will open up.
             
            Copy the below 2 functions, and paste them in there.
             
            Then go back to your spreadsheet, and put in either:

            =LFSplitSpace(A1)    Where A1 is the cell with your string
            to get what you asked for separate by a space.
            Or
            =LFSplitComma(A1)    Where A1 is the cell with your string
            to get what you asked for separate by a comma.

            This will re-compute automatically whenever your string value changes, like any other built in excel function.

            Then do save as, and pick the Excel Macro-Enabled Workbook (*.xlsm).  This is required for spreadsheets with self-written functions or modules such as this.
              (note next "first time" you open this it will ask to enable macros say yes).

            This will work for any length 0-30000 (I think) in Excel 2010, I use 2013.  You will only get results if at least 3 digits (else blank), and this will not have any hanging 2 or 1 digits at end.
            Works like a charm for me.
            Let me know how it goes.

            Function LFSplitSpace(pStr As String) As String
                Dim i, pStrLen As Integer
                Dim RtnVal, P3One As String
                pStrLen = Len(pStr)
                For i = 1 To pStrLen
                    P3One = Mid(pStr, i, 3)
                    If i = 1 Then
                        If Len(P3One) > 2 Then
                            RtnVal = P3One
                        End If
                    Else
                        If Len(P3One) > 2 Then
                            RtnVal = RtnVal & " " & P3One
                        End If
                    End If
                Next i
                LFSplitSpace = RtnVal
            End Function
            Function LFSplitComma(pStr As String) As String
                Dim i, pStrLen As Integer
                Dim RtnVal, P3One As String
                pStrLen = Len(pStr)
                For i = 1 To pStrLen
                    P3One = Mid(pStr, i, 3)
                    If i = 1 Then
                        If Len(P3One) > 2 Then
                            RtnVal = P3One
                        End If
                    Else
                        If Len(P3One) > 2 Then
                            RtnVal = RtnVal & "," & P3One
                        End If
                    End If
                Next i
                LFSplitComma = RtnVal
            End Function

            MrProgrammer  Cool

              lotteryfanatic's avatar - try 20this%202.JPG
              California
              United States
              Member #122106
              January 24, 2012
              924 Posts
              Offline
              Posted: February 21, 2015, 5:32 pm - IP Logged

              Ok, here you go.  Did it first with no programming, but a headache, so better a program function.
               
              In your excel spreadsheet, do a Alt PF11.  Pick Insert > Module from top, a window will open up.
               
              Copy the below 2 functions, and paste them in there.
               
              Then go back to your spreadsheet, and put in either:

              =LFSplitSpace(A1)    Where A1 is the cell with your string
              to get what you asked for separate by a space.
              Or
              =LFSplitComma(A1)    Where A1 is the cell with your string
              to get what you asked for separate by a comma.

              This will re-compute automatically whenever your string value changes, like any other built in excel function.

              Then do save as, and pick the Excel Macro-Enabled Workbook (*.xlsm).  This is required for spreadsheets with self-written functions or modules such as this.
                (note next "first time" you open this it will ask to enable macros say yes).

              This will work for any length 0-30000 (I think) in Excel 2010, I use 2013.  You will only get results if at least 3 digits (else blank), and this will not have any hanging 2 or 1 digits at end.
              Works like a charm for me.
              Let me know how it goes.

              Function LFSplitSpace(pStr As String) As String
                  Dim i, pStrLen As Integer
                  Dim RtnVal, P3One As String
                  pStrLen = Len(pStr)
                  For i = 1 To pStrLen
                      P3One = Mid(pStr, i, 3)
                      If i = 1 Then
                          If Len(P3One) > 2 Then
                              RtnVal = P3One
                          End If
                      Else
                          If Len(P3One) > 2 Then
                              RtnVal = RtnVal & " " & P3One
                          End If
                      End If
                  Next i
                  LFSplitSpace = RtnVal
              End Function
              Function LFSplitComma(pStr As String) As String
                  Dim i, pStrLen As Integer
                  Dim RtnVal, P3One As String
                  pStrLen = Len(pStr)
                  For i = 1 To pStrLen
                      P3One = Mid(pStr, i, 3)
                      If i = 1 Then
                          If Len(P3One) > 2 Then
                              RtnVal = P3One
                          End If
                      Else
                          If Len(P3One) > 2 Then
                              RtnVal = RtnVal & "," & P3One
                          End If
                      End If
                  Next i
                  LFSplitComma = RtnVal
              End Function

              Thank-you so much Sir. I'm still working on it trying to get it to work.


                SergeM's avatar - slow icon.png
                Economy class
                Belgium
                Member #123700
                February 27, 2012
                4035 Posts
                Offline
                Posted: February 22, 2015, 3:20 pm - IP Logged
                523658443269539020140273612345678910111213
                >>5236584432695390201402736

                You use mid() with referencing to other Cells.
                If you need a number coming out, then make it a value.