View Question
Q: Excel Macro to Remove a Number From a Name ( Answered ,   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.```
 ```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: and gave an additional tip of: \$1.00 `The answer was great and did exactly what I needed!`