Solved

Removing digits from exel 2007 column

Posted on 2011-09-05
5
297 Views
Last Modified: 2012-05-12
I want to remove leading numbers from an excel column, e.g. my column is like this
1   AlcoholConcern  
2   AlcoholFocus    
3   AlcoholInfScotl
and l want it like this
AlcoholConcern  
AlcoholFocus    
AlcoholInfScotl

thanks
0
Comment
Question by:mmalik15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 36483676
Hello,

You could use Text to Columns and use the Space as the delimiter

OR

use a formula like

=trim(mid(A1,find(" ",A1)+1,99))

cheers, teylyn

0
 
LVL 19

Expert Comment

by:akoster
ID: 36483685
this can be done in a number of ways, easiest would be to post this formula in cell B1, assuming that the data column is column A.

=MID(A1,5,LEN(A1)-5)

Open in new window


the formula removes the first 4 characters



0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36483686
Exactly what I was about to say but my Submit didn't work for some bizarre reason.

With a slight tweak, instead of hardcoding the 99 for the length, I used LEN(A1).

Thanks
Rob H
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36483692
@akoster, what if the numbers go into the 10s or 100s with three spaces between number and text?

@Rob, right! I like 99. It does the job in most instances and is faster to type. For longer strings I use 999 :-))
0
 
LVL 19

Expert Comment

by:akoster
ID: 36483776
Well, it does the job in most instances and is faster to type. For longer number i use 6 or 7 ;-))
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

756 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