Solved

Excel: how to split text from numbers in cells

Posted on 2014-11-21
6
201 Views
Last Modified: 2014-11-21
Can anyone give a quick hand?
I've got a spreadsheet with scores for nearly 2000 rows in the format: letter, number. eg A1, C2, B1, B4, C3, D5 and so on.  Each cell has only one letter and one number.
I want to split the letters and numbers into adjacent rows.
Is there a formula or method I can apply?
I'm using Excel 2010
0
Comment
Question by:colin_thames
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40457644
Is all of this "A1, C2, B1, B4, C3, D5" in one cell, or in several cells?

Need a bit more specifics.
0
 

Author Comment

by:colin_thames
ID: 40457654
Sorry, each cell in the column has one letter and one number, eg
A1
D4
C3
E5

I want to split each cell into two columns, one for the letter and one for the number
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40457665
If that is column A starting in cell A2, then cell B2 would be

=left(A2,1)

and cell C2 would be

=mid(a2,2,999)
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:colin_thames
ID: 40457697
Perfect, that works, thanks.
Can I just check what the 999 refers to?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40457706
It's just a very long number - much longer than you need, but there's no harm in having such a long number.
0
 

Author Closing Comment

by:colin_thames
ID: 40457735
Many thanks Philip, both for speed and a real simple solution
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.

815 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

10 Experts available now in Live!

Get 1:1 Help Now