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!

The answer was great and did exactly what I needed!