You are here:

Excel/excel return part of cell

Advertisement


Question
QUESTION: I have information listed as a date, name, and amount in a single cell. I used LEFT and RIGHT statements to return just the date and amount. Is there a way to have just the name returned?
i.e. 05/13/12 Frontier, IN 45.62
I want to transfer into three different cells as 05/13/12, Frontier, IN, and 45.62

ANSWER: IF you actually need to split out the data, data, text to columns would be the easiest route as this physically breaks up the data.  If it has to be done with formulas, then the MID function would do it  It gets a little messy though - to get Frontier (if the data starts in A1)

=LEFT(TRIM(RIGHT(A1,LEN(A1)-8)),SEARCH(",",TRIM(RIGHT(A1,LEN(A1)-8)))-1)

and to get the IN

=LEFT(TRIM(RIGHT(A1,LEN(A1)-SEARCH(",",A1))),SEARCH(" ",TRIM(RIGHT(A1,LEN(A1)-SEARCH(",",A1))))-1)

---------- FOLLOW-UP ----------

QUESTION: I do not have to use formulas just did not know of an alternate method. Not sure what you mean by data, text to columns. Would you explain?

Answer
Sorry, in excel prior to 2007 version, it's the menu choices data, followed by text to columns - you would need to select the data first, then use that choice.  In 2007 onwards, click the Data tab and then click the Text To Columns command in the Data Tools group.

You need to ensure you have enough blank columns to the right of your data to take the data - otherwise existing data will get overwritten - if it is only one column of data that shouldn't be an issue, otherwise just insert columns.

I hope this has clarified things but if you need any further help or clarification just let me know.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.