How to separate text cell into columns

**ExcelNewby** · 11-05-2007, 07:55 PM

See attachment.

When I copied my travel expenses into an Excel spreadsheet, all of the data/text was combined into column A. Is there a way or formula that can separate the traveler names, traveling date and $ amount into separate columns?

Example.
02/06/07 AP 018 18810 034551 0600 Domestic Travel Smith 02/02 992.30

Objective:

Column A Column B Column C
Traveler Travel Date Amount
Smith 02/02 992.30

TIA
Excel Newby

**Trebor76** · 11-05-2007, 08:49 PM

Hi there,

Though you could use different functions to do this, you can easily split your text by using Excel's Text to Columns function. To do this, follow these five steps:

1. Highlight the entire range (A1:A29 in your example)
2. From the Data menu select Text to Columns
3. Ensure the Delimited radio button is selected and click the Next > button
4. Click the Tab and Space option boxes and click the Next > button
5. Ensure the cell reference in the Destination text box is $B$1 and then click the Finish button

HTH

Robert

**ExcelNewby** · 11-05-2007, 09:12 PM

Hi Robert,

Thank you for your suggestion unfortunately it doesn't work. I need to be able to separate text from numeric data. What you suggested worked up to a point. The alignment in the last few data fields does not align due to the travelers names varying in length. see example below

02/16/07 AP 066 18810 034551 0600 Domestic Travel Jones 02/01 1,338.22
02/20/07 AP 072 18810 034551 0600 Domestic Travel Griffin-Davison 02/14 1,686.12
02/20/07 AP 072 18810 034551 0600 Domestic Travel Lane 02/16 1,114.84

TIA,

ExcelNewby

**Trebor76** · 11-05-2007, 10:07 PM

Hi there,

There is a probably a more succinct way of doing this (I have to race off to a meeting at the moment), but have a look at the attached where I've used various formulas in Columns B-K (which I've hidden for presentation) to end up with the three columns (L-N) you're after.

HTH

Robert

**Trebor76** · 11-05-2007, 11:44 PM

Hi there,

I'd say (let me know either way) the attached is what you're after.

HTH

Robert

**ExcelNewby** · 11-06-2007, 05:18 AM

Morning Robert-

That's exactly what I wanted the return to be. The problem is that when I tried to paste additional data with different travelers names the formula unable to recognize. Is there a reference cell where I need to update the travelers names?

Thank you for your amazing willingness to give your time and assist clueless Excel users like me.

TIA,
ExcelNewby

**EdMac** · 11-06-2007, 06:24 AM

Hi,

Trebors formula works perfectly - the problem arises in your example because the one line that it does not work for is in a diferent format due to the transaction type - nothing to do with the travellers name, this a journal entry.

Take this transaction out and all is fine.

Ed