?
Solved

Excel - split string

Posted on 2012-08-29
4
Medium Priority
?
617 Views
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?
Sample

column A

Boston  MA
Chicago IL


result

Column A   column B

Boston        MA
Chicago       IL
0
Comment
Question by:VBdotnet2005
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
TazDevil1674 earned 2000 total points
ID: 38346157
Presuming your data is in column A type following in column B & C

Column B
=LEFT(A2,LEN(A2)-3)

Column C
=RIGHT(A2,2)

This will give what you want
0
 
LVL 7

Expert Comment

by:flaphead_com
ID: 38346416
-or- as VBA

Sub Split()
  Sheets("Sheet2").Select
  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
  Loop
   
End Sub
0
 
LVL 18

Expert Comment

by:xtermie
ID: 38349557
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
http://excelhints.com/2010/01/29/using-text-to-columns-to-separate-data/
0
 
LVL 18

Expert Comment

by:xtermie
ID: 38353286
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.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Loops Section Overview

839 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