Results 1 to 5 of 5

Thread: split characters from numbers

  1. #1
    New Member
    Join Date
    Jun 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default split characters from numbers

    Field contains 1990NorcrossRoad
    I need 1990Norcross Road

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: split characters from numbers

    Your "need" appears to only separate 1990Norcross and Road with a blank. That doesn't match your "split characters from numbers" statement.

  3. #3
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: split characters from numbers

    Hi tphillips7278,

    Can you share more data, so that we can identify any pattern, if at all.

    Quote Originally Posted by tphillips7278 View Post
    Field contains 1990NorcrossRoad
    I need 1990Norcross Road

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: split characters from numbers

    This "ugly" formula will extract the numbers from any string in A1 (then fill down for others):

    Code:
    =SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)) * ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A1)))/10)
    In your example, it will extract the 1990.

    If you have Excel 365, this will do it:

    Code:
    =TEXTJOIN(, 1, TEXT(MID(A8, ROW($A$1:INDEX($A$1:$A$1000, LEN(A8))), 1), "#;-#;0;"))
    Last edited by kweaver; Jun 19th, 2019 at 02:53 PM.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,248
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: split characters from numbers

    Here is a UDF (user defined function) that I think will do what you want...
    Code:
    Function InsertBlanks(ByVal Text As String) As String
      Dim X As Long
      For X = Len(Text) To 2 Step -1
        If Mid(Text, X - 1, 2) Like "[0-9a-z][A-Z]" Then
          Text = Left(Text, X - 1) & " " & Mid(Text, X)
        End If
      Next
      InsertBlanks = Text
    End Function
    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use InsertBlanks just like it was a built-in Excel function. For example,

    =InsertBlanks(A1)

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •