Re: Separating text and dates.
- From: Dave Peterson <petersod@xxxxxxxxxxxxxxxx>
- Date: Mon, 21 Aug 2006 11:19:02 -0500
You could use a couple of helper columns:
With that info in B1, you could get the text portion with:
=LEFT(B1,LEN(B1)-11)
And the date portion with:
=DATE(RIGHT(B1,4),MID(RIGHT(B1,10),4,2),MID(RIGHT(B1,10),1,2))
Mark K wrote:
Hi all.
I have data imported from a csv file where one column (B) contains
cells with text and date in the format of "XXXX dd/mm/yyyy". Originally
was using text to columns to split these without a problem, using the
space as the separator. But recently I've been coming across some where
there is a space inside the text which is causing problems.
My current solution is to first search and replace the space in the
text with a hyphen then do the text to columns. So far I have the macro
seaching for and replacing 15 different text entries.
Just wondering if anyone had an easier way to do this?
I used to count back a fixed number of characters from the right (can't
remember exactly how I did it) but then I found it was reversing the day
and the month when separated.
NOTE: My current method works, just looking for a more efficient way of
doing it.
TIA
--
Mark K
------------------------------------------------------------------------
Mark K's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14117
View this thread: http://www.excelforum.com/showthread.php?threadid=573763
--
Dave Peterson
.
- Follow-Ups:
- Re: Separating text and dates.
- From: Mark K
- Re: Separating text and dates.
- References:
- Separating text and dates.
- From: Mark K
- Separating text and dates.
- Prev by Date: Confused - Range and Cells
- Next by Date: Re: Need one miracle
- Previous by thread: Separating text and dates.
- Next by thread: Re: Separating text and dates.
- Index(es):
Relevant Pages
|
