First 3 characters

[Elton Law]

Dear Expert,
I have used the OCR and convert the PDF file to EXCEL.

BpO21Jan200810:56
C Gr 5 Oct 2008 7:58

They display in 2 types which I cannot use "Text to Column" to split all
into individual columns.
What functions can I use so that I can capture the 2nd characters in the
cell please?
What functions can I use so that I can capture the 3nd characters and the
rest after the third characters please ?
First one is easy ... just Left(A1,1)

BpO21Jan200810:56
Then how about ? "P", "O","21Jan2008","10:56" ...

C Gr 5 Oct 2008 7:58
Also, how about this ?
Thanks so much

 

[Jacob Skaria]

Try using the MID() function

BpO21Jan200810:56
MID(A1,4,9) = "21Jan2008"

If this post helps click Yes

 

[Elton Law]

Hi,
I just found I should have put in function page rather than programming forum.
Anyway, yours work on BpO21Jan200810:56
But it does not work on C Gr 5 Oct 2008 7:58
Do you have any idea that solve two cases at the same time ? Thanks

 

[Jacob Skaria]

Try

=MID(SUBSTITUTE(A1," ",""),4,9)

If this post helps click Yes

 

[Elton Law]

That's prefect !!!!!!!!!!!!!
Thanks a lot !

Try

=MID(SUBSTITUTE(A1," ",""),4,9)

If this post helps click Yes

 

[Jacob Skaria]

Assuming you have the data in ColA, try the below which will extract all 4
info..

B1 =SUBSTITUTE(A1," ","")
C1 =LEFT(B1,1)
D1 =MID(B1,2,1)
E1 =MID(B1,4,FIND("200",B1,4))
F1 =TRIM(RIGHT(SUBSTITUTE(B1,E1,REPT(" ",10)),7))

Once you do this copy paste special values....and then you can convert the
date text to date by using =DATEVALUE()..


If this post helps click Yes