Google Answers Logo
View Question
 
Q: Excel Macro to Remove a Number From a Name ( Answered 5 out of 5 stars,   0 Comments )
Question  
Subject: Excel Macro to Remove a Number From a Name
Category: Computers > Programming
Asked by: tigers-ga
List Price: $5.00
Posted: 13 Feb 2003 06:00 PST
Expires: 15 Mar 2003 06:00 PST
Question ID: 160857
I have a excel spreadsheet with about 5000 names with numbers within
the same cell.  I need a macro that I can run to cut the number off
the end of the name and place it in the cell to the right.  I am new
to macros and I am lost.

Here is an example of what I am looking at:

Name                               Number
John S Smith    1234567
Jeff L Lewis  9876541
Steve B Thomas    4567893

These are in the same field.

I need the macro to run and look like this:

Name                               Number
John S Smith                       1234567
Jeff L Lewis                       9876541
Steve B Thomas                     4567893

This macro would pull the digits and move them to the column to the
right.

Thanks for your help!

Request for Question Clarification by hammer-ga on 13 Feb 2003 06:05 PST
Is the number always 7 digits long?

- Hammer

Clarification of Question by tigers-ga on 13 Feb 2003 06:35 PST
98% of the time it is seven numbers.  Sometimes there are none, but I
wouldn't need to worry about those.
Answer  
Subject: Re: Excel Macro to Remove a Number From a Name
Answered By: jeremymiles-ga on 13 Feb 2003 13:04 PST
Rated:5 out of 5 stars
 
I will assume that your values are currently in a column starting with
A1.

The first stage is to ensure that no spaces have crept in at the end
that we couldn't see.  To do this, in column B use:

=ltrim(A1).  

This will remove all double spaces and all spaces from the end.

Then in column C use Len to calculate the length of the string.

=len(B1)

Now we now the length, we know that the number is the last seven
digits, so we can use:

=MID(B1, 1, C1-7)

In column C, to extract the name.

And in column D

=mid(B1,C1 - 6, 7)

This will extract the number.

More elegantly (but with more potential for confusion):

The name can be found with:
=MID(TRIM(A1), 1, (LEN(TRIM(A1))-7))

And the number can be found with:

=MID(TRIM(A1), (LEN(TRIM(A1))-6), 7  )

Search strategy:
Excel help file, under text formulae.

Please feel free to request clarification, if you have trouble
implementing this.

jeremymiles-ga

Request for Answer Clarification by tigers-ga on 14 Feb 2003 05:19 PST
I guess I am confused.  Am I supposed to type the =ltrim(A1) into
column B1 and so on and so forth with the rest of the formulas? 
Because when I do I am getting #VALUE or 0.

Clarification of Answer by jeremymiles-ga on 15 Feb 2003 03:07 PST
Sorry if that was confusing.  Here is a simple version:

If the names are in column A, starting at row 1, in column B type:
=MID(TRIM(A1), 1, (LEN(TRIM(A1))-7)) 

And in column C type:
=MID(TRIM(A1), (LEN(TRIM(A1))-6), 7  ) 

If the names are in a different place, type the reference to the cell
at the top of the column instead of A1.

Then drag the contents down.

If that doesn't work, please try to explain more about where the cells
are in the sheet.

jeremymiles-ga

Request for Answer Clarification by tigers-ga on 18 Feb 2003 05:20 PST
Thanks, it works great!

Clarification of Answer by jeremymiles-ga on 21 Feb 2003 07:43 PST
Thanks for using Google Answers - glad it helped.

And thanks for the tip!
tigers-ga rated this answer:5 out of 5 stars and gave an additional tip of: $1.00
The answer was great and did exactly what I needed!

Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy