VBA - A macro to remove part of the data contained in cells

Ask a question



Issue




I need to remove titles (Mr/Mrs/Miss) preceeding the names in one cell and copy them to another cell. Any idea? Your advice is much appreciated.
  • For example if I have Miss Jane Smith in C2, then in C3: Jane Smith
  • I also need to make the result appear in another sheet.

Solution

  • 1) Try this formula

=MID(C2,SEARCH(" ",C2)+1,LEN(C2)-SEARCH(" ",C2)+1)


This will work whatever be the tile in C2 Mr,Mrs or Miss etc. The logic is find the first blank(that is after the title) and use that position in the mid function.

Type in C2
Mr john smith or Miss Gabriel smith
and see what happen in the formula cell
  • 2) You include sheets name before with an exclamation mark.


Suppose Mr john smith is in sheet 1 cell C2

Then in any other sheet and in any empty copy this formula
=MID(Sheet1!C2,SEARCH(" ",Sheet1!C2)+1,LEN(Sheet1!C2)-SEARCH(" ",Sheet1!C2)+1)

Experiment with this formula

Note that

Thanks to venkat1926 for this tip on this forum.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team