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:

ai2

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.


These Adverts help to pay for this site.

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


Agoda HolidaysAgoda HolidaysThese Adverts help to pay for this site.

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.

 


33.1 - 546,037
DreamHost

Scroll down for Comments
0 0 vote
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