remove salutations

[rakesh seebaruth]

Dear Guys

In Column A from A1:A500, I have e.g. Mr John Smith and Mr Mick Steeve

I need a formula or vba that would remove Mr and Mr and paste it in cell B

Thanks in advance.

regards

rakesh

 

[mandukes]

Re: remove saluations

Hi,

Try this,

=MID(A3,3,100)

 

[Peter_SSs]

Re: remove saluations

Assuming that you could have other length salutations, I would suggest trying

=REPLACE(A1,1,FIND(" ",A1),"")

 

[rakesh seebaruth]

Re: remove saluations

thanks but sorry my question was wrongly address

in cell A1 i have the names MR JACK SMITH AND MR JOHN THOMAS , i need to remove MR AND MR from cell A1 and paste it in Cell B2. i don't need the name and surname.

Thanks

 

[mandukes]

Re: remove saluations

Do you have multiple (more than 2) names in the same cell? or just 2 ?

 

[Peter_SSs]

Re: remove saluations

Further questions on top of mandukes' ..

1. Do you have some cells with only one name?
2. Does every person have such a salutaion?
3. Do you have any salutations other than "Mr" (eg Mrs, Dr, Prof, ...)?
4. If there are 2 (or more) names in a cell, is there always " and " between them, or could it be " & " or something else?
5. What version of Excel are you using?

Finally, it would be very helpful to see a good representative set of dummy sample data and the expected results for that data. Say about 10 rows.

 

[mandukes]

Re: remove saluations

As per your requirement the code works with "Mr" keyword. It would removes all the occurrence of keyword "Mr" in cells.

Sub getRidMr()


Dim mStr  As String
Dim lastrow As Long
lastrow = Cells(Rows.count, "A").End(xlUp).Row
For i = 1 To lastrow
mStr = ActiveSheet.Range("A" & i)
mStr = Replace(mStr, "Mr", "")       '[COLOR=#ff0000] Replace the salutation to suit [/COLOR]
ActiveSheet.Range("B" & i) = mStr
Next i

 

[rakesh seebaruth]

Re: remove saluations

hi Peter

Please refer the following link https://www.dropbox.com/s/y1ahasea8ajebjf/TESTING212.xlsx?dl=0
expected results are in cells B2

in reply to your questions above

(1) i have twenty eight cells with different names
(2) each person their title differs
(3) There are salutations like Mr, Mr and Mrs ,Mrs ,Mr and Mr
(4) There is always "AND" between the names there is no Dr,Prof ,&
(5) Excel 2010.

What i am trying to do you is mail merge . I have to address letters to Bank Customers .Their titles are Mr ,Mrs ,Miss, Mr and Mr( if two borrowers have availed of a loan)

thanks/regards

rakesh

 

[rakesh seebaruth]

Re: remove saluations

thanks

i don't want to get rid of the salutation Mr indeed i need to keep it i need to discard the name and surname.

Please refer the following link https://www.dropbox.com/s/y1ahasea8a...G212.xlsx?dl=0
expected results are in cells B2.

Thanks/regards

rakesh

 

[Peter_SSs]

Re: remove saluations

Thanks for the sample data. It isn't much like the sample in post #1 though since all cells have other text before the first salutation and there are many instances of multiple spaces between words.

Due to the multiple spaces between words, I have employed a helper column.
I have also assumed from the sample data that ..
- All data is in upper case
- Never more than two names in a cell.

Excel Workbook
	A	B	C
1	CUST_NAME	TRIM SPACES	RESULT
2	NUCK MR MOH IRS	NUCK MR MOH IRS	MR
3	NUCK MR MOH IRS	NUCK MR MOH IRS	MR
4	IYA MR PAINE AND MRS MARIE LOUISE	IYA MR PAINE AND MRS MARIE LOUISE	MR AND MRS
5	IYA MR PAINE AND MRS MARIE LOUISE	IYA MR PAINE AND MRS MARIE LOUISE	MR AND MRS
6	HU MR KAI AND MRS SHARM	HU MR KAI AND MRS SHARM	MR AND MRS
7	BOO MR CHAND AND MRS PRIS	BOO MR CHAND AND MRS PRIS	MR AND MRS
8	BOO MR CHAND AND MRS PRIS	BOO MR CHAND AND MRS PRIS	MR AND MRS
9	RAMLO MRS PRAT	RAMLO MRS PRAT	MRS
10	SOO MR SANDEV SINGH AND MRS MEE	SOO MR SANDEV SINGH AND MRS MEE	MR AND MRS
11	SAHO MR AB ABE AND MRS MAR	SAHO MR AB ABE AND MRS MAR	MR AND MRS
12	GHOO MISS YEA	GHOO MISS YEA	MISS
13	NUCK MR MOH IRS	NUCK MR MOH IRS	MR
14	NUCK MR MOH IRS	NUCK MR MOH IRS	MR
15	IYA MR PAINE AND MRS MARIE LOUISE	IYA MR PAINE AND MRS MARIE LOUISE	MR AND MRS
16	IYA MR PAINE AND MRS MARIE LOUISE	IYA MR PAINE AND MRS MARIE LOUISE	MR AND MRS
17	HU MR KAI AND MRS SHARM	HU MR KAI AND MRS SHARM	MR AND MRS
18	BOO MR CHAND AND MRS PRIS	BOO MR CHAND AND MRS PRIS	MR AND MRS
19	BOO MR CHAND AND MRS PRIS	BOO MR CHAND AND MRS PRIS	MR AND MRS
20	RAMLO MRS PRAT	RAMLO MRS PRAT	MRS
21	JACKE MR SMITH AND MR JOHN BEE1	JACKE MR SMITH AND MR JOHN BEE1	MR AND MR
22	JACKS MR SMITH AND MR JOHN BEE2	JACKS MR SMITH AND MR JOHN BEE2	MR AND MR
23	JACKY MR SMITH AND MR JOHN BEE3	JACKY MR SMITH AND MR JOHN BEE3	MR AND MR
24	JACKEET MR SMITH AND MR JOHN BEE4	JACKEET MR SMITH AND MR JOHN BEE4	MR AND MR
25	JACKRE MR SMITH AND MR JOHN BEE5	JACKRE MR SMITH AND MR JOHN BEE5	MR AND MR
Extract Salutation