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:


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.

 


43.1 - 622,684
Scroll down for Comments
0 0 votes
Article Rating
Subscribe
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments