Extract data

[Afarag]

Dears,



Please i have some data that i want to extract the time only from the cells as shown

37030Amal Ahmed Shaban83... 7:00 AM 4:00 PM
39557Radwa Arafat Abdel Mo... 7:00 AM 4:00 PM
41494Shaimaa Adel Mohame... 7:00 AM 4:00 PM
42149Aya Fathy Abdallah Aly... 7:00 AM 4:00 PM
42339Youmna Mohamed Abd... 7:00 AM 4:00 PM
6538Mohamed Mostafa Khalil... 7:00 AM 4:00 PM
8926Nagham Mohamed El sa... 7:00 AM 4:00 PM
8946Zinab Salah Abd el Shafi... 7:00 AM 4:00 PM
14659Nagwa Elsayed Abdel A... 8:00 AM 5:00 PM
14848Esraa Hosny Hamza83... 8:00 AM 5:00 PM
14854Alaa Mahmoud Hussein... 8:00 AM 5:00 PM
Created Tuesday, March 11, 2014 12:41:37 PM. Page 1 of 7
Shifts per day 3/13/2014
Name Start time End time Note


i want to extract 7:00 AM 4:00 PM in another column etc...

VBA code is preferable

 

[sgmpatnaik]

@Afarag

Try the below Formula

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,")",""),".",""),FIND(":",A1)-5,FIND(":",REPLACE(A1,99,FIND(":",A1),""))-1))

if any problem please let us know

Thanks

 

[iferror]

Formula Solution:
Column 1
=MID(A5;FIND("|";SUBSTITUTE(A5;" ";"|";LEN(A5)-LEN(SUBSTITUTE(A5;" ";""))-3))+1; FIND("|";SUBSTITUTE(A5;" ";"|";LEN(A5)-LEN(SUBSTITUTE(A5;" ";""))-1)) - FIND("|";SUBSTITUTE(A5;" ";"|";LEN(A5)-LEN(SUBSTITUTE(A5;" ";""))-3)) )
Column 2
=RIGHT(A5;LEN(A5)-FIND("|";SUBSTITUTE(A5;" ";"|";LEN(A5)-LEN(SUBSTITUTE(A5;" ";""))-1)))

VBA Solution:
Just pointing you in the right direction, do not hesitate to ask for more help if needed

Sub test()
dummy = [A5]
dummyarr = Split(dummy, " ")
MsgBox dummyarr(UBound(dummyarr) - 3) & "" & dummyarr(UBound(dummyarr) - 2)
MsgBox dummyarr(UBound(dummyarr) - 1) & "" & dummyarr(UBound(dummyarr))
End Sub

 

[Afarag]

Dears,

it's the best answer

Thanks a lot,

 

[Afarag]

@iferror

i have it via formula, i was need it via VBA
any way your follow up is highly appreciated

try this in B1



=IF(ISNUMBER(VALUE(MID(RIGHT(TRIM(A1),15),1,1))),MID(RIGHT(TRIM(A1),15),1,7),"")



try this in C1



=IF(ISNUMBER(VALUE(MID(RIGHT(TRIM(A1),15),1,1))),TRIM(RIGHT(A1,7)),"")



try this in D1



=IF(ISNUMBER(VALUE(MID(RIGHT(TRIM(A1),15),1,1))),TRIM(RIGHT(A1,15)),"")


Gratefully