Feb 02, 2016
I was recently asked how the street number and street name could be split into two cells (eg 344 Queen Street to 344 in one cell and Queen Street in another) using Microsoft Excel. The Text to Columns Wizard would split it into three cells because it would split the data at the position of both spaces.
The answer lies with Text Functions. There are 24 functions in the Text category. The ones we need in this case are:
=LEFT() Returns the leftmost characters from a text value
=FIND() Finds one text value within another (case-sensitive)
=RIGHT() Returns the rightmost characters from a text value
=LEN() Returns the number of characters in a text string
Number: To find the street number we need to find the position of the first space and subtract one to determine how many characters from the left to display.
Name: To find the street name we need to determine the length of the string of text, find the position of the first space and deduct the first number from the second to determine how many characters from the right to display.
For example:
A | B | C | |
1 | 344 Queen Street | =LEFT(A1,FIND(” “,A1)-1) | =RIGHT(A1,LEN(A1)-FIND(” “,A1)) |
Would give you:
A | B | C | |
1 | 344 Queen Street | 344 | Queen Street |
If you are interested in more Text Functions as well as other types of functions, see New Horizons' Excel training courses.
How do your Excel skills stack up?
Test NowNext up:
- Office Politics
- Convert a Column to a Link-to-Item in SharePoint
- A big flop that broke the rules and broke the records
- Custom number formats in Excel
- Configuring an internet facing deployment for Microsoft CRM Server
- Remember Me? In one way I hope not!
- Slugging it out, and the need to bleed
- Service Design in ITIL®
- The magic of scrollbar control in Excel
- Adding videos to your website
Previously
- Creating an awesome In-Memory Database
- How can we re-habit Change?
- Cyber Resilience – Where do we start?
- Taking Responsibility for Your Own Performance
- Using Text Functions in Excel
- The importance of a break-even point in budgeting
- Your Future in IT – A “Brave New World” for the IT Professional
- All high performing work teams deliver these ten elements
- Creating a basic Angular directive
- Did you make a ritual?