Merging text in two adjacent cells 1

[johnbarr44]

I have two columns, Surname and Given Name.
Given Name column contains a mixture of
initials, (some with words) e.g. A, AB, AB and CD,
and
words e.g. Alan, John and Fred, Bros, Brothers.

Wherever the Given Name is "Bros" or "Brothers" I would like to cut that word from the Given Name column and paste it (after a space) onto whatever word (or words) appears in the Surname column.
I have some 40,000 rows of data to process, so would welcome any suggestions for a formula or macro that could accomplish this.

I have tried "IF" function =IF(A1="Brothers",B1+" Brothers",A1) but it will not accept the B1+" Brothers" value.

John B

 

[johnbarr44]

Sorry, should have mentioned I'm using Excel 2003.

John B

 

[dhookom]

Try use the ampersand

=IF(A1="Brothers",B1&" Brothers",A1)

Duane
Hook'D on Access
MS Access MVP

 

[johnbarr44]

Thanks Duane, that did it!


John B