Parsing Data

  • Thread starter Thread starter Saxman
  • Start date Start date
S

Saxman

I need to separate digits from text.

e.g.

16:05:00 Warwick

14:20:00 Carlisle

So that I end up with 16:05:00 in one column and Warwick in another.

Ideally, 16:05 in one column and Warwick in another.

TIA
 
Hi Saxman,

Am Sat, 09 Jul 2011 11:53:45 +0100 schrieb Saxman:
16:05:00 Warwick

14:20:00 Carlisle

So that I end up with 16:05:00 in one column and Warwick in another.

Ideally, 16:05 in one column and Warwick in another.
Click to expand...

make sure that on the right side of your values is an empty column. Then
click in the top of your column => Data => Text to columns. Format the
column with the time hh:mm


Regards
Claus Busch
 
On Sat, 09 Jul 2011 11:53:45 +0100, Saxman You can use the
Data/Text-to-Columns Tool using a <space> delimiter. (How to access the
tool depends on your version of Excel).
Then format the "time" column as hh:mm
Click to expand...

That works fine Ron. I didn't know the option existed.

One minor thing, with the following, Las ended up in a third column. Not
too bothered about that as an indicator exists. I could always use
find/replace to correct.

14:20:00 Ffos Las
 
Hi Saxman,

Am Sat, 09 Jul 2011 17:33:22 +0100 schrieb Saxman:
One minor thing, with the following, Las ended up in a third column. Not
too bothered about that as an indicator exists. I could always use
find/replace to correct.

14:20:00 Ffos Las
Click to expand...

with Data => Text to columns you can choose "Fixed Width" and put the
seperator between time and city.


Regards
Claus Busch
 
with Data => Text to columns you can choose "Fixed Width" and put the
seperator between time and city.
Click to expand...

I did that thanks. Saves a find/replace. I remembered the empty column
on your advice, otherwise one has to shift the headers one place to the
right.
 
What Claus said: Used the "Fixed Width" option instead the Delimiter.
Then place the dividing line just after the time stamp.

By the way, if the time stamp can ever represent 24 hours or greater,
format the cells as: [hh]:mm This allows times greater than 24 to be
properly represented.
Click to expand...

Thank you. I've taken your advice on board.
 
Back
Top