Building an Excel Macro

[tenz]

I have a spread sheet with 3 columns:

Column A: First and Last Name
Column B: Address
Column C: Phone Number

I am trying to import this information into our database, but to import it I have to have the first and last name in separate columns. Does anyone know how to build a macro to that would put First Name in column A and Last Name in Column B? So, in the end my table would look like:

Column A: First Name
Column B: Last Name
Column C: Address
Column D: Phone Number

Thank you in advance!

 

[SF0751]

Check out the following earlier post:

thread68-398012

Good luck! Susan
Under certain circumstances, profanity provides a relief denied even to prayer. - Mark Twain

 

[acron]

Maybe you can do this without a macro.

Insert a new column B, so that the address is pushed into Column C. Now select Column A, and goto the Data menu, select Text To Columns, select Delimitedas the Data type, and after clickinh Next, select Space as the delimiter.

Excel shouls then split the contents of Column A over A and B. This assumes that all cells in A contain onlt two names, i.e. just on space. IOf there are any cells with more tahn two spaces, you should insert enough columns to acter for each part of the name.

A.C.

 

[DaleWatson123321]

tenz,

The primary "challenge" of such situations, is where the LAST name can be one of those names that has more than one "word" ... e.g. John Van Den Hengel.

To allow for such cases, I've written 6 formulas that you can insert beside the existing column that contains the existing First and Last Names.

These formulas can then be copied down for each row that contains names, and it will work properly for LAST names that contain up to THREE words.

You didn't mention the possibility of a "Middle" name, so I hope for your sake that this is NOT a possibility. Otherwise, it would NOT be possible to determine whether the "middle" word is part of the "Middle Name" or part of the "Last Name.

I hope this helps. I can email you the file. Just email me and I'll send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

tenz,

Last night was a LATE night for me - watching the election results. After a "cup of caffeine" to wake me up , I came to the (perhaps obvious) realization...

...that REGARDLESS of how many words are part of the LAST name, if there is only one FIRST Name and NO Middle Name, then following the FIRST Name, ALL of the remaining words HAVE to be part of the LAST Name.

Therefore the following two formulas will be all you need...

Step 1) If your Combined Name is Column is in Column A, with headings on row 1, then insert two columns to the right of Column A.

Step 2) Enter "Last Name" in cell B1.

Step 3) Enter "First Name" in cell C1.

Step 4) Enter the following formula in cell B2:
=MID(A2,FIND(" ",$A2,1)+1,999)

Step 5) Enter the following formula in cell C2:
=MID(A2,1,FIND(" ",$A2,1)-1)

Step 6) Copy the formulas down for the number of names in Column A.

Step 7) Highlight columns B and C, and Copy

Step 8) Use Edit - Paste Special - Values

Step 9) Delete Column A.

I hope this helps Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca