98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Splitting Text In Cells
Splitting Text In Cells
Resolved · Low Priority · Version Standard
Julia has attended:
Excel Consulting course
Excel Consulting course
Splitting Text In Cells
Hi
I have a column containing peoples full names eg Julia Foster.
How can I split these into first and last names in adjacent cells.
Julia
RE: Splitting Text In Cells
Hi Julia
To split a a persons full name in a cell use the following code:
If the Name is in cell A1 then enter code as followis in the adjacent cells:
In B1 enter =LEFT(A1,FIND(" ",A1)-1)
The LEFT function returns all characters in a string starting from the left to a defined number.
The FIND function returns the position of the space. This indicates to the LEFT function how many characters to display
By placing the -1 after the bracket we are telling the LEFT function to return the first name BUT NOT include the Space.
In C1 enter =MID(A2,FIND(" ",A2)+1,100)
The MID function returns all characters in a string starting from a predefined position in a string.
The FIND function returns the position of the space. This indicates to the MID function from where to start displaying the rest of the text.
By placing the +1 after the bracket in the middle section we are telling the MID function to return the last name BUT NOT include the Space. The 100 is to indicate how far to display the text. It allows for a very long name.
I have attached a spreadsheet as an example.
Hope this helps
Carlos
Attached files...
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Switch on smart tags – Excel 2007In order to use smart tags, make sure they are turned on, to do this: |