Removing middle name/initial from a cell!

J

jesswoodm

New Member
Joined
Jul 7, 2011
Messages
5
I have a database that I created with First Name, Initial/Middle Name, and Last Name (all in one cell).

I am now trying to update this database, but the new records I am trying to insert are only First Name, Last Name (all in one cell).

I want to change my existing database to only have First Name, Last Name in the same cell. Also, I want to cross reference my new cells with the existing database in order to ensure I have not duplicated by having an initial in one of the cells.

I have basic knowledge of macros and formatting etc and have played around with several potential solutions, but I'm not having much luck. I managed to separate the existing database cells, so now I have one column which contains either just the first name, or first name last name, depending on whether the person had a middle initial entered.

Please help!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Try this formula. assume your names are in A1

Code: 
=TRIM(LEFT(A1,FIND(" ",LOWER(A1),1))) & " " & TRIM(MID(A1,FIND(" ",LOWER(A1),FIND(" ",LOWER(A1),1)+1)+1,LEN(A1)-FIND(" ",LOWER(A1),1)+1))
 
Upvote 0
Thanks for your help. However, I am unsure where this goes. I got my whole list, complete with first name, initial and last name in A1:A4400. Then I pasted the cell into B1. Is this the right place? It came up with an error, and the same when I tried to use it as a macro.

Any further help would be really appreciated!
 
Upvote 0
Hi and welcome to the Board
This was posted previously by someone who I can't give credit to...but I reckon it's brilliant

Try
Highlight the column in question and then
Edit > Replace with Find: ' * ' and Replace: ' '
So, Find SPACE followd by * followed by SPACE

Replace SINGLE Space
 
Upvote 0
If it's any consolation...your formula works fine for me !
 
Upvote 0
I'm sure it does work, I just don't have the knowledge to make this happen.

I put my list in Column A, rows 1-4400. Then I pasted

=TRIM(LEFT(A1,FIND(" ",LOWER(A1),1))) & " " & TRIM(MID(A1,FIND(" ",LOWER(A1),FIND(" ",LOWER(A1),1)+1)+1,LEN(A1)-FIND(" ",LOWER(A1),1)+1))

into B1. It then says #VALUE, and gives me this when I click on 'more info':


Correct a #VALUE! error
Occurs when the wrong type of argument or operand is used.

Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears.
Review the possible causes and solutions




Am I doing something wrong? Thanks for your help.
 
Upvote 0
Does this formula do what you want?

=LEFT(A1,FIND(" ",A1))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
 
Upvote 0
As a matter of curiosity, did you try the FIND / REPLACE method.
I'd be interested in feedback, mainly from the point of when NOT to use it, or when it's not suitable
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
How to do I change last name to last intial
2
Replies
12
Views
162
Joe4
Joe4
C
  • Question
Take full name from one worksheet, make appear as first name last initial in another
Replies
3
Views
191
awoohaw
awoohaw
A
  • Question
Create a Macro for identifying duplicate
Replies
7
Views
223
Joe4
Joe4
K
  • Solved
Change "Last name, first initial" to first name last name in sentences
Replies
2
Views
770
Peter_SSs
Peter_SSs
X
  • Question
Macro to fill an identifier down and SUMIF
Replies
4
Views
167
xbrianx
X

Forum statistics

Threads
1,214,945
Messages
6,122,397
Members
449,081
Latest member
JAMES KECULAH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top