|
First 3 characters
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 |
First 3 characters
Try using the MID() function
BpO21Jan200810:56 MID(A1,4,9) = "21Jan2008" If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: 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 |
First 3 characters
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" wrote: Try using the MID() function BpO21Jan200810:56 MID(A1,4,9) = "21Jan2008" If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: 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 |
First 3 characters
Try
=MID(SUBSTITUTE(A1," ",""),4,9) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: 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" wrote: Try using the MID() function BpO21Jan200810:56 MID(A1,4,9) = "21Jan2008" If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: 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 |
First 3 characters
That's prefect !!!!!!!!!!!!!
Thanks a lot ! "Jacob Skaria" wrote: Try =MID(SUBSTITUTE(A1," ",""),4,9) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: 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" wrote: Try using the MID() function BpO21Jan200810:56 MID(A1,4,9) = "21Jan2008" If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: 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 |
First 3 characters
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 --------------- Jacob Skaria "Elton Law" wrote: 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 |
| All times are GMT +1. The time now is 05:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com