Using FIND, LEFT and MID to format names

by Joe Carr

What do you do if you require names in a different format to what you have?

For example…

All of your names are in the datasource as follows:

And you want

Well, the key is to use string functions to find the gap between the first name and surname.

However, the relative character position of the gap may be different between different names

So we must use a function which specifically finds the “ ” for each row’s specific string.

There are a number which can do this.

First, FIND.

Find (string, substring) will return the numerical character position of the substring.

For example, Find (“Birley Paul”, “ “) will return 7.

To do this on a whole database, we would input the name Field where the string goes in the example syntax. This in turn would return row specific values for each name.

So with knowledge of where each space is, we can use two other functions, LEFT and MID, which use specific character positions to return different parts of the whole string.

LEFT will return the first N values of a string

So we can extract the first name from Customer Name by specifying the LEFT function to stop returning characters when it reaches the space. So we use the FIND function shown above and also subtract 1 (otherwise it will include the space in the new field).

MID will return the last characters of a string from the Nth position in it (left-to-right).

So Mid(“Birley Paul”, 7) will return “ Paul”. Note that this includes the space.

So opposite to the LEFT function above, we must add 1 onto the space position within MID:

So now we have first name and last name in two separate fields.

You can then can combine the fields with a new calculated field. Quotation marks are useful for altering the formatting by inputting punctuation or space. For example:

Will return:

As another example:

Will return:

Finally, remember to click on the arrow when calculating new fields to find information on all the functions you can use within them. This is really useful for learning the syntax.

This will open the view below:

I hope you found this useful!