Split cell conents and copy to new cells

K

KokoroAyo

New Member
Joined
Sep 8, 2017
Messages
14
Hi,

I have a workbook containing some data. Some of the columns contain more than 1 data put together in 1 cell. Now I have responsibility to separate them and make a new sheet with each item in its own column.

The sheet contains 11 columns
Columns 2, 4, 8 and 9 contain mixed entries
Col2 = FACILITY_CODE and FACILITY_NAME separated with space
Col4 = CLIENT_NAME and PHONE separated with space
Col5 =CODE_NUMBER separated with space (Some cells say 'Declined'- this is valid, some include extra data with code (00000 (Update)) indicating the code is an update, In this case, the Update is to be moved leaving only Codenumber and Declined
Col8 = CALLER_NAME and CALLER_NUMBER separated with space (some cells not spaced, some have only NUMBER data item)
Col9=APPROVER, ENTEREDBY and TIME (APPROVER and RECEPTIONIST are separated with / while TIME is separated with space. So we have it like this: Name/Name 00:00AM) Some cells have just Name

My task is to create new sheet (16 columns) and
1. separate Col 2 and move FACILITY_CODE to Col 2, FACILITY_NAME to Col 3
2. separate Col 4 and move CLIENT_NAME to Col 4, PHONE to Col 5
3. separate Col 5 (if there is (Update)) and move CODE_NUMBER to Col 7, "(Update)" to Col 8
4. separate Col 8 and move CALLER_NAME to Col 12, CALLER_NUMBER to Col 13
9. separate Col 9 and move APPROVER to Col 14, ENTEREDBY to Col 15, TIME to Col 16

All other corresponding Items will be placed under corresponding Column Headings. Only Column 3 will be ignored.

I need help with this please.

This is Sheet 1

[TABLE="width: 1885"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]FACILITY CODE AND NAME[/TD]
[TD]STATE[/TD]
[TD]CLIENT NAME AND NUMBER[/TD]
[TD]CLIENTID[/TD]
[TD]CODE NUMBER[/TD]
[TD]PROBLEM[/TD]
[TD]CALLER[/TD]
[TD]APPROVER[/TD]
[TD]CODE STATUS[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2016[/TD]
[TD]EN/0119 University of Nigeria, Nsukka[/TD]
[TD]Enugu[/TD]
[TD] ALI EUPHAMIA 08134260357[/TD]
[TD="align: right"]3184084[/TD]
[TD="align: right"]11026210[/TD]
[TD]Cyesis for obst uss[/TD]
[TD]08065780527 Azuka[/TD]
[TD]Dr.Nnaji/Chinyere 3:25pm[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2016[/TD]
[TD]EN/0119 University of Nigeria, Nsukka[/TD]
[TD]Enugu[/TD]
[TD]OKEKE NKIRUKA 07035529673[/TD]
[TD="align: right"]2810098[/TD]
[TD="align: right"]11026211[/TD]
[TD]Cyesis for obst uss[/TD]
[TD]08065780527 Azuka[/TD]
[TD]Dr.Nnaji/Chinyere 3:25pm[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2016[/TD]
[TD]EN/0119 University of Nigeria, Nsukka[/TD]
[TD]Enugu[/TD]
[TD] EDAFIOGHO NNEKA 08034443217[/TD]
[TD="align: right"]3184049[/TD]
[TD="align: right"]11026212[/TD]
[TD]Cyesis for obst uss[/TD]
[TD]08065780527 Azuka[/TD]
[TD]Dr.Nnaji/Chinyere 3:25pm[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2016[/TD]
[TD]KW/0027 UITH[/TD]
[TD]Kwara[/TD]
[TD] ALABI AHMED 08060539866[/TD]
[TD="align: right"]3242897[/TD]
[TD="align: right"]11026213[/TD]
[TD]Musculoskeletal trauma secondary to RTA for Emergency[/TD]
[TD]09097343199 Usman[/TD]
[TD]Dr.Nnaji/Uche 5:45pm[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2016[/TD]
[TD]KW/0027 UITH[/TD]
[TD]Kwara[/TD]
[TD] ASHAOLU COMFORT 08142839966[/TD]
[TD="align: right"]1451836[/TD]
[TD="align: right"]11026214[/TD]
[TD]Refractive error for opthalmologist consult[/TD]
[TD]9097343199 Usman[/TD]
[TD]Dr.Nnaji/Uche 5:45pm[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2016[/TD]
[TD]KW/0027 UITH[/TD]
[TD]Kwara[/TD]
[TD] ASHAOLU SUSANNA 08142839966[/TD]
[TD="align: right"]1451836[/TD]
[TD="align: right"]11026215[/TD]
[TD]Poor vision for opthalmologist consult[/TD]
[TD]9097343199 Usman[/TD]
[TD]Dr.Nnaji/Uche 5:45pm[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2016[/TD]
[TD]KW/0027 UITH[/TD]
[TD]Kwara[/TD]
[TD]AJIA KAMILU 08060539866[/TD]
[TD="align: right"]3242928[/TD]
[TD="align: right"]11026216[/TD]
[TD]Lower back pain for orthopedic consult[/TD]
[TD]9097343199 Usman[/TD]
[TD]Dr.Nnaji/Uche 5:45pm[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2016[/TD]
[TD]EN/0175 THE GOOD SHEPHERD SPECIALIST HOSPITAL[/TD]
[TD]Enugu[/TD]
[TD] ISHIWU ESTHER 08033316111[/TD]
[TD="align: right"]2772734[/TD]
[TD="align: right"]11026217[/TD]
[TD]Abdominal pain for abdo pelvic scan[/TD]
[TD]08164863199 Chidinma[/TD]
[TD]Dr.Nnaji/Uche 5:45pm[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]31/10/2016[/TD]
[TD]OY/0252 Jaja/UCH[/TD]
[TD]Oyo[/TD]
[TD] OLUWASANU IFEOLUWA 08029188937[/TD]
[TD="align: right"]2873231[/TD]
[TD="align: right"]11026218[/TD]
[TD]Dilated cardiomyopathy for follow up[/TD]
[TD]08100805359Akinbade[/TD]
[TD]Dr.Nnaji/Uche 6:05pm[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]FCT/0044 Pison Hospital[/TD]
[TD]FCT[/TD]
[TD]EMMANUEL CHRISTY 8036781779[/TD]
[TD="align: right"]408709[/TD]
[TD="align: right"]11026219[/TD]
[TD]******l Candiditis ? PID For HVS[/TD]
[TD]070622926324 Titi[/TD]
[TD]Dr.Nnaji/Bosun 9:03am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]NG/0020 Standard Hospital[/TD]
[TD]Niger[/TD]
[TD]OLADOKUN OYEKUNLE 08065646955[/TD]
[TD="align: right"]2771894[/TD]
[TD="align: right"]11026220[/TD]
[TD]HBV For LFT[/TD]
[TD]08025253948 Shola[/TD]
[TD]Dr.Nnaji/Bosun 9:11am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]FCT/0002 Arewa Specialist Hospital & Diagnostic Centre/Maitama District Hosp[/TD]
[TD]FCT[/TD]
[TD] OKORO KALU 08023807791[/TD]
[TD="align: right"]2844583[/TD]
[TD="align: right"]11026221[/TD]
[TD]? Cervical Sponylosis For Cervical Spine X-Ray[/TD]
[TD="align: right"]8187646838[/TD]
[TD]Dr.Nnaji/Bosun 9:11am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]FCT/0132 Kings Care Hospital[/TD]
[TD]FCT[/TD]
[TD] AZORO CHIAMANDA 08094282408[/TD]
[TD="align: right"]2844680[/TD]
[TD]Declined[/TD]
[TD]ARTI For[/TD]
[TD="align: right"]8171054644[/TD]
[TD]Dr.Nnaji/Bosun 9:11am[/TD]
[TD]Code Declined[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]OS/0019 Adebare Specialist Hospital / LAUTECH[/TD]
[TD]Osun[/TD]
[TD]OGBANKITI MARY 08068374293[/TD]
[TD="align: right"]2792387[/TD]
[TD="align: right"]11026222[/TD]
[TD]Breech presentation at term for elective C/S[/TD]
[TD]08090545345 Akanbi[/TD]
[TD]Dr.Nnaji/Chinyere 9:44am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]OY/0252 Jaja/UCH[/TD]
[TD]Oyo[/TD]
[TD] ADENIYI-AOGO ESTHER 08053106183[/TD]
[TD="align: right"]1638681[/TD]
[TD="align: right"]11026223[/TD]
[TD]Plantar sascitis for expert mgt[/TD]
[TD]08033843871 Ande[/TD]
[TD]Dr.Nnaji/Chinyere 9:59am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]NW/0001 May Day Specialist Hospital[/TD]
[TD]Nasarwa[/TD]
[TD] IDAH EBERECHUKWU 08036369393[/TD]
[TD="align: right"]2190900[/TD]
[TD="align: right"]11026224[/TD]
[TD]Cyesis for obst uss, hbsag,hcv[/TD]
[TD]8107650361 Gloria[/TD]
[TD]Dr.Nnaji/Chinyere 10:03am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]FCT/0044 Pison Hospital / Kubwa General Hospital[/TD]
[TD]FCT[/TD]
[TD] RAZAQ FATIMA 8062173066[/TD]
[TD="align: right"]3234286[/TD]
[TD="align: right"]11026225[/TD]
[TD]Persistent leg foot pain for orthopedic surgeon consult[/TD]
[TD]08169091061 Titi[/TD]
[TD]Dr.Nnaji/Bosun 10:10am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]FCT/0546 Garki Hospital[/TD]
[TD]FCT[/TD]
[TD]USMAN ZAINAB 07063765858[/TD]
[TD="align: right"]2935582[/TD]
[TD="align: right"]11026226[/TD]
[TD]Secondary infertility for HSG[/TD]
[TD]08174636864 HELEN[/TD]
[TD]Dr.Nnaji/Bosun 10:16am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]GM/0002 Federal Medical Centre Gombe[/TD]
[TD]Gombe[/TD]
[TD]ABUBAKAR MARYAM 07061538169[/TD]
[TD="align: right"]3299998[/TD]
[TD="align: right"]11026227[/TD]
[TD][/TD]
[TD]07057751736 Usman[/TD]
[TD]Dr.Nnaji/Chinyere 11:05am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]PL/0102 Plateau Specialist Hospital[/TD]
[TD]Plateau[/TD]
[TD] DAUDA RABI 08034830681[/TD]
[TD="align: right"]3009995[/TD]
[TD="align: right"]11026228[/TD]
[TD]Cyesis for obst uss[/TD]
[TD]08168578911 Simeon[/TD]
[TD]Dr.Nnaji/Chinyere 11:08am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]NW/0098 Livia Shammah Hospitals Limited[/TD]
[TD]Nasarwa[/TD]
[TD] EGWUAGHA DANIEL 08023754149[/TD]
[TD="align: right"]1557730[/TD]
[TD]Declined[/TD]
[TD]Rashes And Catarrh[/TD]
[TD][/TD]
[TD]Dr.Nnaji/Chinyere 11:08am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]OY/0252 Jaja Clinic/UCH[/TD]
[TD]Oyo[/TD]
[TD]ADEBAYO RALIAT 08034344304[/TD]
[TD="align: right"]2173441[/TD]
[TD="align: right"]11026229[/TD]
[TD]Congestive Cardiac Failure For Follow Up[/TD]
[TD]08142664000 Joseph[/TD]
[TD]Dr.Nnaji/Bosun 11:50am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]LA/0136 Gold Cross Hospital[/TD]
[TD]Lagos[/TD]
[TD]SALIU RUKAYAT 08073342107[/TD]
[TD="align: right"]2534118[/TD]
[TD]11026176 (Update)[/TD]
[TD]Acute Appendicitis For General Surgeon Consult[/TD]
[TD]08082094409 Paul[/TD]
[TD]Dr.Nnaji/Bosun 11:50am[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]LA/0526 Lagoon Hospital/Eko hospital[/TD]
[TD]Lagos[/TD]
[TD]YUSUF NURAT 08033000794[/TD]
[TD="align: right"]1557735[/TD]
[TD]11026027 (Update)[/TD]
[TD]Cervical lesion ? Cervicitis for O& G follow up, pap smear,pap smear cytology,pelvic uss[/TD]
[TD]09090832136 Jenifer[/TD]
[TD]Dr.Nnaji/Chinyere 12:48PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]FCT/0390 Federal Staff Hospital - Jabi[/TD]
[TD]FCT[/TD]
[TD]SONIBARE OLADAYO 07082293804[/TD]
[TD="align: right"]3282452[/TD]
[TD="align: right"]11026230[/TD]
[TD]Lymphadenopathy for soft tissue of hand scan[/TD]
[TD]7060725608 Michael[/TD]
[TD]Dr.Nnaji/Chinyere 1:05PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]FCT/0158 Nisa Premier Hospital[/TD]
[TD]FCT[/TD]
[TD] IBRAHIM AMINA 08056883070[/TD]
[TD="align: right"]420811[/TD]
[TD="align: right"]11026231[/TD]
[TD]Cholelithiasis for admission, LFT,EUCR,FBC, abdominopelvic uss[/TD]
[TD]08090457745Michael[/TD]
[TD]Dr.Nnaji/Chinyere 1:15PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]KD/0010 Giwa Hospital And Specialist Clinic[/TD]
[TD]Kaduna[/TD]
[TD] BELLO JAFARU 08056122806[/TD]
[TD="align: right"]3295915[/TD]
[TD="align: right"]11026232[/TD]
[TD]Dental Caries fordentist consult[/TD]
[TD]7039105217 Abdullahi[/TD]
[TD]Dr.Nnaji/Bosun 1:35PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]EN/0119 University of Nigeria, Nsukka[/TD]
[TD]Enugu[/TD]
[TD] AIGBODION KING 08034124850[/TD]
[TD="align: right"]2936404[/TD]
[TD="align: right"]11026233[/TD]
[TD]Cryptorchidism for pediatric surgeon review and mgt[/TD]
[TD]08065780527 Azuka[/TD]
[TD]Dr.Nnaji/Chinyere 1:45PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]EN/0119 University of Nigeria, Nsukka[/TD]
[TD]Enugu[/TD]
[TD]OMAH ESTHER 08064445115[/TD]
[TD="align: right"]3178734[/TD]
[TD="align: right"]11026234[/TD]
[TD]Breast lump for breast scan[/TD]
[TD]08065780527 Azuka[/TD]
[TD]Dr.Nnaji/Chinyere 1:45PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]EN/0119 University of Nigeria, Nsukka[/TD]
[TD]Enugu[/TD]
[TD] EGWUAGU NKIRUKA 07069796033[/TD]
[TD="align: right"]2170728[/TD]
[TD="align: right"]11026235[/TD]
[TD]Cyesis for HBSAg[/TD]
[TD]08065780527 Azuka[/TD]
[TD]Dr.Nnaji/Chinyere 1:55PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]EN/0119 University of Nigeria, Nsukka[/TD]
[TD]Enugu[/TD]
[TD]EZIRIM ADANNA 08169180614[/TD]
[TD="align: right"]3489976[/TD]
[TD="align: right"]11026236[/TD]
[TD]Cyesis for obst uss[/TD]
[TD]08065780527 Azuka[/TD]
[TD]Dr.Nnaji/Chinyere 1:55PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]EN/0119 University of Nigeria, Nsukka[/TD]
[TD]Enugu[/TD]
[TD] CHUKWUDI IJEOMA 08033572147[/TD]
[TD="align: right"]2936415[/TD]
[TD="align: right"]11026237[/TD]
[TD]Cyesis for obst uss[/TD]
[TD]08065780527 Azuka[/TD]
[TD]Dr.Nnaji/Chinyere 2:00PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]OY/0252 Jaja/UCH[/TD]
[TD]Oyo[/TD]
[TD] OSASONA DEBORAH 08029181087[/TD]
[TD="align: right"]1638989[/TD]
[TD="align: right"]11026238[/TD]
[TD]Simple goiter for follow up[/TD]
[TD]08142664000 Joseph[/TD]
[TD]Dr.Nnaji/Chinyere 2:05PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]EN/0119 University of Nigeria, Nsukka[/TD]
[TD]Enugu[/TD]
[TD]MBEGBU CHEKWUBE7061888741[/TD]
[TD="align: right"]2858252[/TD]
[TD="align: right"]11026239[/TD]
[TD]Cyesis for obst uss[/TD]
[TD]08065780527 Azuka[/TD]
[TD]Dr.Nnaji/Chinyere 2:12PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]EN/0119 University of Nigeria, Nsukka[/TD]
[TD]Enugu[/TD]
[TD] EZEIBE NGOZI 08063290695[/TD]
[TD="align: right"]1570859[/TD]
[TD="align: right"]11026240[/TD]
[TD]? Inflammatory Breast swelling for breast scan[/TD]
[TD]08065780527 Azuka[/TD]
[TD]Dr.Nnaji/Chinyere 2:25PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]KD/0010 Giwa Hospital And Specialist Clinic[/TD]
[TD]Kaduna[/TD]
[TD] LAWAS BATA 08038289849[/TD]
[TD="align: right"]2936553[/TD]
[TD="align: right"]11026241[/TD]
[TD]Pelvic mass r/o uterine fibroid for pelvic uss, Gynae consult[/TD]
[TD]7039105217 Abdullahi[/TD]
[TD]Dr.Nnaji/Chinyere 2:40PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]OY/0252 Jaja/UCH[/TD]
[TD]Oyo[/TD]
[TD]OYETADE SOLOMON 08053515331[/TD]
[TD="align: right"]1458673[/TD]
[TD="align: right"]11026242[/TD]
[TD]Bradycardia ? Cause for ECG[/TD]
[TD]08142664000 Joseph[/TD]
[TD]Dr.Nnaji/Chinyere 2:05PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]FCT/0394 St Mary Catholic Hospital, Gwagwalada[/TD]
[TD]FCT[/TD]
[TD] NWOSU CHIKA 08062080340[/TD]
[TD="align: right"]2935623[/TD]
[TD="align: right"]11026243[/TD]
[TD]? UTI urine mcs[/TD]
[TD]08173452760 Joseph[/TD]
[TD]Dr.Nnaji/Chinyere 3:11PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]NG/0020 Standard Hospital/IBB Specialist[/TD]
[TD][/TD]
[TD] UBATU CHUKWUNWIKE 07035615982[/TD]
[TD="align: right"]2867093[/TD]
[TD="align: right"]11026244[/TD]
[TD]Hypospavia For Padiatrician Consult[/TD]
[TD]08025253948 Shola[/TD]
[TD]Dr.Nnaji/Bosun 3:45PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]PL/0158 Dee Medical Centre[/TD]
[TD]Plateau[/TD]
[TD] DANSHAK UKAIKOH 09055045538[/TD]
[TD="align: right"]2888408[/TD]
[TD="align: right"]11026245[/TD]
[TD]? UTI for Urine MSC, HVS MCS & Pelvic Scan/FBC[/TD]
[TD]08068124159 Evylen[/TD]
[TD]Dr.Nnaji/Bosun 4:02PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]PL/0158 Dee Medical Centre[/TD]
[TD]Plateau[/TD]
[TD]OKO SAMUEL 08036143092[/TD]
[TD="align: right"]2888433[/TD]
[TD="align: right"]11026246[/TD]
[TD]Enteric Fever In A Known Diabetic Patient For FBC, Stool & Urine MCS & HBA1C[/TD]
[TD]08068124159 Evylen[/TD]
[TD]Dr.Nnaji/Bosun 4:02PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]PL/0158 Dee Medical Centre[/TD]
[TD]Plateau[/TD]
[TD]OKO ALVIN 08036143092[/TD]
[TD="align: right"]2888433[/TD]
[TD="align: right"]11026247[/TD]
[TD]Typhoid for Stool MCS FBC, Blood Culture And Sencitivitis[/TD]
[TD]08068124159 Evylen[/TD]
[TD]Dr.Nnaji/Bosun 4:02PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]KD/0010 Giwa Hospital And Specialist[/TD]
[TD]Kaduna[/TD]
[TD]ALIYU HAFSAT 08065392450[/TD]
[TD="align: right"]1873232[/TD]
[TD]11026082 (Update)[/TD]
[TD]DM in Pregnancy For gynae follow up[/TD]
[TD]07066687279 Aisha[/TD]
[TD]Dr.Nnaji/Chinyere 4:42PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]FCT/0573 Kelina Medical Center[/TD]
[TD]FCT[/TD]
[TD] ANUMBA FRIDAY 08033139341[/TD]
[TD="align: right"]2887380[/TD]
[TD="align: right"]11026248[/TD]
[TD]Wax impaction for routine syringind[/TD]
[TD]08129908324 Linda[/TD]
[TD]Dr.Nnaji/Chinyere 4:58PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]KD/0010 Giwa Hospital And Specialist Clinic[/TD]
[TD]Kaduna[/TD]
[TD] LAWAS BATA 07063735370[/TD]
[TD="align: right"]2936553[/TD]
[TD]11026241(update)[/TD]
[TD]uterine fibroid, ? PID for serum prolactin, HVS M/C/S[/TD]
[TD]7039105217 Aisha[/TD]
[TD]Dr.Nnaji/Christie 6:40PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2016[/TD]
[TD]FCT/0120 Limi Hospital[/TD]
[TD]FCT[/TD]
[TD]OGBONNA JOY 080357377212856239[/TD]
[TD="align: right"]2856239[/TD]
[TD="align: right"]11026249[/TD]
[TD]UTI& preterm labour for urine m/c/s, obst scan, Admission[/TD]
[TD]08090160175 nkem[/TD]
[TD]Dr.Nnaji/Christie 8:20PM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]02/11/2016[/TD]
[TD]FCT/0158 Nisa Premier Hospital[/TD]
[TD]FCT[/TD]
[TD] MOHAMMED MARIAM 08032090302[/TD]
[TD="align: right"]2844706[/TD]
[TD]11026156(update)[/TD]
[TD]malaria in pregnancy r/o sepsis for FBC[/TD]
[TD]07034179894 victor[/TD]
[TD]Dr.Nnaji/Christie 6:43aM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]02/11/2016[/TD]
[TD]FCT/0158 Nisa Premier Hospital/National hospital[/TD]
[TD]FCT[/TD]
[TD] MOHAMMED MARIAM 08032090302[/TD]
[TD="align: right"]2844706[/TD]
[TD="align: right"]11026250[/TD]
[TD]severe tension headache in preg for neurologist consult[/TD]
[TD]07034179894 victor[/TD]
[TD]Dr.Nnaji/Christie 6:43aM[/TD]
[TD]Code Received[/TD]
[/TR]
[TR]
[TD="align: right"]02/11/2016[/TD]
[TD]FCT/0158 Nisa Premier Hospital[/TD]
[TD]FCT[/TD]
[TD] KAYODE FRANCISCA 08035064588[/TD]
[TD="align: right"]2534656[/TD]
[TD]11026052 (update)[/TD]
[TD]?PID, cyesis, Rt pyelonephritis for FBC, trans******l scan[/TD]
[TD]7034179894 Austin[/TD]
[TD]Dr.Nnaji/Christie 7:05am[/TD]
[TD]Code Received[/TD]
[/TR]
</tbody>[/TABLE]



And this is Sheet 2

[TABLE="width: 1712"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]FACILITY_CODE[/TD]
[TD]FACILITY_NAME[/TD]
[TD]CLIENT_NAME[/TD]
[TD]CLIENT_PHONE_NO[/TD]
[TD]CLIENTID[/TD]
[TD]REF_CODE_NUMBER[/TD]
[TD]CODE_TYPE[/TD]
[TD]CODE_STATUS[/TD]
[TD]DECLINE_REASON[/TD]
[TD]PROBLEM[/TD]
[TD]CALLER_NAME[/TD]
[TD]CALLER_PHONE[/TD]
[TD]APPROVER[/TD]
[TD]ENTEREDBY[/TD]
[TD]ENTRY_TIME[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
This will do some of what you want
Code: 
Sub splitData()
   Dim lr As Long
   Dim cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   lr = Ws1.Range("A" & Rows.Count).End(xlUp).Row
   
   Ws2.Range("A2:A" & lr).Value = Ws1.Range("A2:A" & lr).Value
   With Ws2.Range("C2:C" & lr)
      .Value = Ws1.Evaluate(Replace("if({1},mid(@,find("" "",@)+1,999))", "@", .Offset(, -1).Address))
   End With
   With Ws2.Range("B2:B" & lr)
      .Value = Ws1.Evaluate(Replace("if({1},left(@,find("" "",@)))", "@", .Address))
   End With
   For Each cl In Ws2.Range("D2:D" & lr)
      cl.Value = Left(Trim(Ws1.Range(cl.Address)), InStrRev(Trim(Ws1.Range(cl.Address)), " "))
      cl.Offset(, 1).Value = Right(Trim(Ws1.Range(cl.Address)), Len(Trim(Ws1.Range(cl.Address))) - InStrRev(Trim(Ws1.Range(cl.Address)), " "))
   Next cl
   Ws2.Range("F2:F" & lr).Value = Ws1.Range("E2:E" & lr).Value
   With Ws2.Range("G2:G" & lr)
      .Value = Ws1.Evaluate(Replace("if(isnumber(search(""("",@)),value(left(@,search(""("",@)-1)),@)", "@", .Offset(, -1).Address))
   End With
   With Ws2.Range("H2:H" & lr)
      .Value = Ws1.Evaluate(Replace("if(isnumber(search(""("",@)),right(@,len(@)-search(""("",@)+1),"""")", "@", .Offset(, -2).Address))
   End With
   Ws2.Range("I2:J" & lr).Value = Ws1.Range("J2:K" & lr).Value
   Ws2.Range("K2:K" & lr).Value = Ws1.Range("G2:G" & lr).Value
   With Ws2.Range("L2:L" & lr)
      .Value = Ws1.Evaluate(Replace("if(isnumber(@),"""",right(@,len(@)-search("" "",@)))", "@", .Offset(, -4).Address))
   End With
   With Ws2.Range("M2:M" & lr)
      .Value = Ws1.Evaluate(Replace("if(isnumber(@),@,left(@,search("" "",@)-1))", "@", .Offset(, -5).Address))
   End With
   With Ws2.Range("N2:N" & lr)
      .Value = Ws1.Evaluate(Replace("if({1},left(@,search(""/"",@)-1))", "@", .Offset(, -5).Address))
   End With
   With Ws2.Range("O2:O" & lr)
      .Value = Ws1.Evaluate(Replace("if({1},mid(@,search(""/"",@)+1,search("" "",@)-search(""/"",@)))", "@", .Offset(, -6).Address))
   End With
   With Ws2.Range("P2:P" & lr)
      .Value = Ws1.Evaluate(Replace("if({1},right(@,len(@)-search("" "",@)))", "@", .Offset(, -7).Address))
   End With

End Sub
But you will need to do some manual alterations, because of the state of your data.
 
Upvote 0
Thank you very much. This provided the answer I was looking for. Shall do the rest as required.

Thank you once again. I'm grateful.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top