Spreadsheet Formula to replace Space with Hyphen

How to replace a Space with a Hyphen in a Spreadsheet..

One easy way to replace any character with another, is to use the Find and Replace function, but that is not always suitable.

However, using a cell with a formula can give you the instant result when you enter data into another cell, without any further effort.  Very useful if updating a spreadsheet regularly.

For example:

You are creating a list of web page page URL’s, based on the web page title, and you need to add a cell with the correct URL.

You start off with a page called: Page Title, (in cell A2), and you need to add it to your domain name eg: abcdiamond.com (in cell A1) to get the result abcdiamond.com/page-title

You can easily use concatenate to add the two together, but you end up with spaces that are not wanted.
eg: +concatenate(a2,”/”,a1) gives you abcdiamond.com/Page Title

You need to change the space to a hyphen, for the better URL.

So, you use the substitute formula, to change a space to a hyphen: SUBSTITUTE(A1;” “;”-“), with the concatenate command to add the two together.

• =+CONCATENATE(B1;”/”;(SUBSTITUTE(A1;” “;”-“))) to produce: abcdiamond.com/Page-Title

But then you might want to have all LOWER characters, so you add in the change to LOWER case command with this combined formula:

• =+CONCATENATE(B1;”/”;(LOWER(SUBSTITUTE(A1;” “;”-“)))) with this result: abcdiamond.com/page-title

The above codes are used in Open Office Spreadsheets, but I would have used the following in MS Excel

• =+CONCATENATE(B1,”/”,(SUBSTITUTE(A1,” “,”-“))) to produce: abcdiamond.com/Page-Title

But then you might want to have all LOWER characters, so you add in the change to LOWER case command with this combined formula:

• =+CONCATENATE(B1,”/”,(LOWER(SUBSTITUTE(A1,” “,”-“)))) with this result: abcdiamond.com/page-title

The difference being the field dividers: , or ;

Summary:

The simple changing of a space to a hyphen is:  =+SUBSTITUTE(A1;” “;”-“)

A more complex change, with adding two cells together, would use this formula: =+CONCATENATE(B1,”/”,(LOWER(SUBSTITUTE(A1,” “,”-“))))

This changes a space to a hyphen, and changes to the cell content to lower case, with the two cells being added together with a forward slash (/) in between the two.