Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Excel split numbers and letters

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 26-02-2009
Member
 
Join Date: Feb 2009
Location: fareham
Posts: 7
Excel split numbers and letters
  

I have a column with a combination of numbers and letters ( numbers always first ), eg

Column A

100M
10N
1U
21LR

Text to columns does not work, all I wish to do is seperate the numbers and letters into seperate cells.

Reply With Quote
  #2  
Old 26-02-2009
Member
 
Join Date: Aug 2008
Posts: 281
Re: Excel split numbers and letters

I don't know how to do this but a simple google search got me this result

http://www.ehow.com/how_2102165_spli...-ms-excel.html

I hope this is what you are looking for!
Reply With Quote
  #3  
Old 26-02-2009
Member
 
Join Date: Jun 2008
Posts: 96
Re: Excel split numbers and letters

Split the text at specified positions, e.g. the string 'ABCDE' split at positions 1, 2 and 5 would result in 'A', 'BCD' and 'E'. The positions should be specified in the Position list and the direction of counting is specified using the Count Position from option below.

http://www.adeptscience.co.uk/produc...TextColumn.htm
Reply With Quote
  #4  
Old 26-02-2009
Member
 
Join Date: Feb 2009
Location: fareham
Posts: 7
Re: Excel split numbers and letters

Thanks for your efforts, but one of your suggestions is text to columns, but this doesnt recognise the difference between letters and numbers. The other suggestion, I could not find a route for the function. I am looking for a formula or macro that will carry out this function.
Reply With Quote
  #5  
Old 08-10-2009
msq msq is offline
Member
 
Join Date: Oct 2009
Posts: 1
Re: Excel split numbers and letters

I had the same problem & couldn't find a solution. Just in case you still want to know, I did the following:-

Copy the data into 2 columns.

In 1 column set a macro, using find & replace, to replace each letter of the aplhabet, 1 at a time, with nothing.

In the 2nd column set a macro using the same method, to replace each number from 1-0 with nothing.

You are then left with a column of numbers, & a column of letters.

Not v.techy but works for me.

I copied the Visual Basic data below to save you 5 mins:-
Macro 1 - gets rid of alphabet


ub Clear_name()
'
' Clear_name Macro
'

'
Selection.Replace What:="a", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="b", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="c", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="d", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="e", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="f", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="g", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="h", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="i", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="j", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="k", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="l", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="m", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="n", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="o", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="p", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="q", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="r", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="s", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="t", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="u", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="v", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="w", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="x", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="y", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="z", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub



Macro 2 - gets rid of numbers

Sub Macro1()
'
'
' Keyboard Shortcut: Ctrl+e
'
Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="9", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="8", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="7", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="6", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="5", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="4", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="3", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="2", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="1", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Excel split numbers and letters"
Thread Thread Starter Forum Replies Last Post
How to change negative numbers to positive numbers in Excel Shaina Na Microsoft Project 3 08-01-2012 04:35 PM
Sony Vaio Laptop shows numbers in place of some letters during typing. Tufan Portable Devices 5 12-08-2011 09:07 AM
Windows Phone 7 - Want to change my screen lock to letters as well as numbers Eddu Portable Devices 3 03-11-2010 10:49 PM
Letters on the keyboard typing as numbers Raulf Hardware Peripherals 3 16-11-2009 11:59 PM
Laptop Keyboard types only Numbers instead of Letters Actionguy Hardware Peripherals 3 03-09-2009 06:14 PM


All times are GMT +5.5. The time now is 01:00 AM.