Removing single variable characters within a cell

[jnathan]

I need to remove the letter within the middle of a persons name.

For example, if the person is called 'Michael J Fox' then it needs to be edited to show only 'Michael Fox', if the person is called 'George W Bush' then only 'George Bush' should be displayed etc. However the persons name should remain as is if a middle character initial does not exist.

Can anyone help with a formula or macro to resolve this issue?

Any help will be really appreciated.

Many thanks

Justin R Nathan

 

[lrobbo314]

=if(len(j1)-len(substitute(j1," ",""))>1,left(j1,find(" ",j1)-1)&" "&right(j1,len(j1)-find(" ",j1,find(" ",j1)+1)),j1)

 

[Rick Rothstein]

I need to remove the letter within the middle of a persons name.

For example, if the person is called 'Michael J Fox' then it needs to be edited to show only 'Michael Fox', if the person is called 'George W Bush' then only 'George Bush' should be displayed etc. However the persons name should remain as is if a middle character initial does not exist.

Can anyone help with a formula or macro to resolve this issue?

Select the entire column of names, then press CTRL+H to bring up the Replace dialog box... type <space>?<space> using a single space character in place of each <space> that I showed (so you will type a space, a question mark and then another space) into the "Find what" field... type a single space into the "Replace with" field... click the "Options>>" button and make sure there is NO checkmark in the "Match entire cell contents" checkbox... finish off by clicking the "Replace All" button.

 

[lrobbo314]

Wow. That's cool. You learn something new every day. And I would bet that you're not going to get any better perfomance than that using formulas or macros.

The only problem is if the data has various periods after the middle initial as happens often with data entry. Sometimes they put the period, sometimes not.

But, that's fixed easily enough by changing the search criteria from " ? " to " ?. "

Nice Rick.

 

[jnathan]

Many thanks, Rick!

Nice, simple and works a treat!!!

 

[jnathan]

Thanks 4 your suggestion too!