Excel - split string

Posted on 2012-08-29
Last Modified: 2012-09-04
I want to get the last two char from a column in Excel file and move to the next column. What is the fomular?

column A

Boston  MA
Chicago IL


Column A   column B

Boston        MA
Chicago       IL
Question by:VBdotnet2005
    LVL 9

    Accepted Solution

    Presuming your data is in column A type following in column B & C

    Column B

    Column C

    This will give what you want
    LVL 7

    Expert Comment

    -or- as VBA

    Sub Split()
      Cells(1, 1).Select
      Do Until IsEmpty(ActiveCell)
        ACV = ActiveCell.Value
        NextCol = Split(ACV, " ")
        ActiveCell.Offset(0, 1).Value = NextCol(1)
        ActiveCell.Offset(1, 0).Select
    End Sub
    LVL 16

    Expert Comment

    You can easily parse text with inherent Excel functionality.  
    If the two letters are always at the end of the column, you can use the built in functionality of Excel, text to columns.  This is under the Data tab.
    Select Delimited and Check that the data is split by a space.
    Hassle free!

    Here are some detailed info
    LVL 16

    Expert Comment

    Hey no luck with this...I think that the text to columns functionality is exactly what you need in your case....easily done even for a large volume of data.  If you need detailed instructions let me know so I can post instructions.  Also, lmk which version of excel you are using.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    This is an explanation of a simple data model to help parse a JSON feed
    In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    27 Experts available now in Live!

    Get 1:1 Help Now