Share

Separating A String of Numbers

Avatar

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

Sure, what version of excel are you using?

Avatar
In response to MrProgrammer

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

MrProgrammer's avatar - Lottery-001.jpg

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.

Avatar
In response to MrProgrammer

Thanks a million!!!

MrProgrammer's avatar - Lottery-001.jpg

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

Avatar
In response to MrProgrammer

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

SergeM's avatar - slow icon.png
523658443269539020140273612345678910111213
>>5236584432695390201402736

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

TopEnd of thread (1 page)

Welcome Guest

Your last visit: Thu, Sep 16, 2021, 1:46 pm

Log In

Log InCancel

Forgot your username?

Forgot your password?