Parsing Data

[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

 

[Claus Busch]

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.

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

 

[Saxman]

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

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

 

[Claus Busch]

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

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


Regards
Claus Busch

 

[Ron Rosenfeld]

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

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.

 

[Saxman]

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

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.

 

[Saxman]

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.

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