Split cell conents and copy to new cells

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

DATEFACILITY CODE AND NAMESTATECLIENT NAME AND NUMBERCLIENTIDCODE NUMBERPROBLEMCALLERAPPROVERCODE STATUS
31/10/2016EN/0119 University of Nigeria, NsukkaEnugu ALI EUPHAMIA 08134260357318408411026210Cyesis for obst uss08065780527 AzukaDr.Nnaji/Chinyere 3:25pmCode Received
31/10/2016EN/0119 University of Nigeria, NsukkaEnuguOKEKE NKIRUKA 07035529673281009811026211Cyesis for obst uss08065780527 AzukaDr.Nnaji/Chinyere 3:25pmCode Received
31/10/2016EN/0119 University of Nigeria, NsukkaEnugu EDAFIOGHO NNEKA 08034443217318404911026212Cyesis for obst uss08065780527 AzukaDr.Nnaji/Chinyere 3:25pmCode Received
31/10/2016KW/0027 UITHKwara ALABI AHMED 08060539866324289711026213Musculoskeletal trauma secondary to RTA for Emergency09097343199 UsmanDr.Nnaji/Uche 5:45pmCode Received
31/10/2016KW/0027 UITHKwara ASHAOLU COMFORT 08142839966145183611026214Refractive error for opthalmologist consult9097343199 UsmanDr.Nnaji/Uche 5:45pmCode Received
31/10/2016KW/0027 UITHKwara ASHAOLU SUSANNA 08142839966145183611026215Poor vision for opthalmologist consult9097343199 UsmanDr.Nnaji/Uche 5:45pmCode Received
31/10/2016KW/0027 UITHKwaraAJIA KAMILU 08060539866324292811026216Lower back pain for orthopedic consult9097343199 UsmanDr.Nnaji/Uche 5:45pmCode Received
31/10/2016EN/0175 THE GOOD SHEPHERD SPECIALIST HOSPITALEnugu ISHIWU ESTHER 08033316111277273411026217Abdominal pain for abdo pelvic scan08164863199 ChidinmaDr.Nnaji/Uche 5:45pmCode Received
31/10/2016OY/0252 Jaja/UCHOyo OLUWASANU IFEOLUWA 08029188937287323111026218Dilated cardiomyopathy for follow up08100805359AkinbadeDr.Nnaji/Uche 6:05pmCode Received
01/11/2016FCT/0044 Pison HospitalFCTEMMANUEL CHRISTY 803678177940870911026219******l Candiditis ? PID For HVS070622926324 TitiDr.Nnaji/Bosun 9:03amCode Received
01/11/2016NG/0020 Standard HospitalNigerOLADOKUN OYEKUNLE 08065646955277189411026220HBV For LFT08025253948 SholaDr.Nnaji/Bosun 9:11amCode Received
01/11/2016FCT/0002 Arewa Specialist Hospital & Diagnostic Centre/Maitama District HospFCT OKORO KALU 08023807791284458311026221? Cervical Sponylosis For Cervical Spine X-Ray8187646838Dr.Nnaji/Bosun 9:11amCode Received
01/11/2016FCT/0132 Kings Care HospitalFCT AZORO CHIAMANDA 080942824082844680DeclinedARTI For8171054644Dr.Nnaji/Bosun 9:11amCode Declined
01/11/2016OS/0019 Adebare Specialist Hospital / LAUTECHOsunOGBANKITI MARY 08068374293279238711026222Breech presentation at term for elective C/S08090545345 AkanbiDr.Nnaji/Chinyere 9:44amCode Received
01/11/2016OY/0252 Jaja/UCHOyo ADENIYI-AOGO ESTHER 08053106183163868111026223Plantar sascitis for expert mgt08033843871 AndeDr.Nnaji/Chinyere 9:59amCode Received
01/11/2016NW/0001 May Day Specialist HospitalNasarwa IDAH EBERECHUKWU 08036369393219090011026224Cyesis for obst uss, hbsag,hcv8107650361 GloriaDr.Nnaji/Chinyere 10:03amCode Received
01/11/2016FCT/0044 Pison Hospital / Kubwa General HospitalFCT RAZAQ FATIMA 8062173066323428611026225Persistent leg foot pain for orthopedic surgeon consult08169091061 TitiDr.Nnaji/Bosun 10:10amCode Received
01/11/2016FCT/0546 Garki HospitalFCTUSMAN ZAINAB 07063765858293558211026226Secondary infertility for HSG08174636864 HELENDr.Nnaji/Bosun 10:16amCode Received
01/11/2016GM/0002 Federal Medical Centre GombeGombeABUBAKAR MARYAM 0706153816932999981102622707057751736 UsmanDr.Nnaji/Chinyere 11:05amCode Received
01/11/2016PL/0102 Plateau Specialist HospitalPlateau DAUDA RABI 08034830681300999511026228Cyesis for obst uss08168578911 SimeonDr.Nnaji/Chinyere 11:08amCode Received
01/11/2016NW/0098 Livia Shammah Hospitals LimitedNasarwa EGWUAGHA DANIEL 080237541491557730DeclinedRashes And CatarrhDr.Nnaji/Chinyere 11:08amCode Received
01/11/2016OY/0252 Jaja Clinic/UCHOyoADEBAYO RALIAT 08034344304217344111026229Congestive Cardiac Failure For Follow Up08142664000 JosephDr.Nnaji/Bosun 11:50amCode Received
01/11/2016LA/0136 Gold Cross HospitalLagosSALIU RUKAYAT 08073342107253411811026176 (Update)Acute Appendicitis For General Surgeon Consult08082094409 PaulDr.Nnaji/Bosun 11:50amCode Received
01/11/2016LA/0526 Lagoon Hospital/Eko hospitalLagosYUSUF NURAT 08033000794155773511026027 (Update)Cervical lesion ? Cervicitis for O& G follow up, pap smear,pap smear cytology,pelvic uss09090832136 JeniferDr.Nnaji/Chinyere 12:48PMCode Received
01/11/2016FCT/0390 Federal Staff Hospital - JabiFCTSONIBARE OLADAYO 07082293804328245211026230Lymphadenopathy for soft tissue of hand scan7060725608 MichaelDr.Nnaji/Chinyere 1:05PMCode Received
01/11/2016FCT/0158 Nisa Premier HospitalFCT IBRAHIM AMINA 0805688307042081111026231Cholelithiasis for admission, LFT,EUCR,FBC, abdominopelvic uss08090457745MichaelDr.Nnaji/Chinyere 1:15PMCode Received
01/11/2016KD/0010 Giwa Hospital And Specialist ClinicKaduna BELLO JAFARU 08056122806329591511026232Dental Caries fordentist consult7039105217 AbdullahiDr.Nnaji/Bosun 1:35PMCode Received
01/11/2016EN/0119 University of Nigeria, NsukkaEnugu AIGBODION KING 08034124850293640411026233Cryptorchidism for pediatric surgeon review and mgt08065780527 AzukaDr.Nnaji/Chinyere 1:45PMCode Received
01/11/2016EN/0119 University of Nigeria, NsukkaEnuguOMAH ESTHER 08064445115317873411026234Breast lump for breast scan08065780527 AzukaDr.Nnaji/Chinyere 1:45PMCode Received
01/11/2016EN/0119 University of Nigeria, NsukkaEnugu EGWUAGU NKIRUKA 07069796033217072811026235Cyesis for HBSAg08065780527 AzukaDr.Nnaji/Chinyere 1:55PMCode Received
01/11/2016EN/0119 University of Nigeria, NsukkaEnuguEZIRIM ADANNA 08169180614348997611026236Cyesis for obst uss08065780527 AzukaDr.Nnaji/Chinyere 1:55PMCode Received
01/11/2016EN/0119 University of Nigeria, NsukkaEnugu CHUKWUDI IJEOMA 08033572147293641511026237Cyesis for obst uss08065780527 AzukaDr.Nnaji/Chinyere 2:00PMCode Received
01/11/2016OY/0252 Jaja/UCHOyo OSASONA DEBORAH 08029181087163898911026238Simple goiter for follow up08142664000 JosephDr.Nnaji/Chinyere 2:05PMCode Received
01/11/2016EN/0119 University of Nigeria, NsukkaEnuguMBEGBU CHEKWUBE7061888741285825211026239Cyesis for obst uss08065780527 AzukaDr.Nnaji/Chinyere 2:12PMCode Received
01/11/2016EN/0119 University of Nigeria, NsukkaEnugu EZEIBE NGOZI 08063290695157085911026240? Inflammatory Breast swelling for breast scan08065780527 AzukaDr.Nnaji/Chinyere 2:25PMCode Received
01/11/2016KD/0010 Giwa Hospital And Specialist ClinicKaduna LAWAS BATA 08038289849293655311026241Pelvic mass r/o uterine fibroid for pelvic uss, Gynae consult7039105217 AbdullahiDr.Nnaji/Chinyere 2:40PMCode Received
01/11/2016OY/0252 Jaja/UCHOyoOYETADE SOLOMON 08053515331145867311026242Bradycardia ? Cause for ECG08142664000 JosephDr.Nnaji/Chinyere 2:05PMCode Received
01/11/2016FCT/0394 St Mary Catholic Hospital, GwagwaladaFCT NWOSU CHIKA 08062080340293562311026243? UTI urine mcs08173452760 JosephDr.Nnaji/Chinyere 3:11PMCode Received
01/11/2016NG/0020 Standard Hospital/IBB Specialist UBATU CHUKWUNWIKE 07035615982286709311026244Hypospavia For Padiatrician Consult08025253948 SholaDr.Nnaji/Bosun 3:45PMCode Received
01/11/2016PL/0158 Dee Medical CentrePlateau DANSHAK UKAIKOH 09055045538288840811026245? UTI for Urine MSC, HVS MCS & Pelvic Scan/FBC08068124159 EvylenDr.Nnaji/Bosun 4:02PMCode Received
01/11/2016PL/0158 Dee Medical CentrePlateauOKO SAMUEL 08036143092288843311026246Enteric Fever In A Known Diabetic Patient For FBC, Stool & Urine MCS & HBA1C08068124159 EvylenDr.Nnaji/Bosun 4:02PMCode Received
01/11/2016PL/0158 Dee Medical CentrePlateauOKO ALVIN 08036143092288843311026247Typhoid for Stool MCS FBC, Blood Culture And Sencitivitis08068124159 EvylenDr.Nnaji/Bosun 4:02PMCode Received
01/11/2016KD/0010 Giwa Hospital And SpecialistKadunaALIYU HAFSAT 08065392450187323211026082 (Update)DM in Pregnancy For gynae follow up07066687279 AishaDr.Nnaji/Chinyere 4:42PMCode Received
01/11/2016FCT/0573 Kelina Medical CenterFCT ANUMBA FRIDAY 08033139341288738011026248Wax impaction for routine syringind08129908324 LindaDr.Nnaji/Chinyere 4:58PMCode Received
01/11/2016KD/0010 Giwa Hospital And Specialist ClinicKaduna LAWAS BATA 07063735370293655311026241(update)uterine fibroid, ? PID for serum prolactin, HVS M/C/S7039105217 AishaDr.Nnaji/Christie 6:40PMCode Received
01/11/2016FCT/0120 Limi HospitalFCTOGBONNA JOY 080357377212856239285623911026249UTI& preterm labour for urine m/c/s, obst scan, Admission08090160175 nkemDr.Nnaji/Christie 8:20PMCode Received
02/11/2016FCT/0158 Nisa Premier HospitalFCT MOHAMMED MARIAM 08032090302284470611026156(update)malaria in pregnancy r/o sepsis for FBC07034179894 victorDr.Nnaji/Christie 6:43aMCode Received
02/11/2016FCT/0158 Nisa Premier Hospital/National hospitalFCT MOHAMMED MARIAM 08032090302284470611026250severe tension headache in preg for neurologist consult07034179894 victorDr.Nnaji/Christie 6:43aMCode Received
02/11/2016FCT/0158 Nisa Premier HospitalFCT KAYODE FRANCISCA 08035064588253465611026052 (update)?PID, cyesis, Rt pyelonephritis for FBC, trans******l scan7034179894 AustinDr.Nnaji/Christie 7:05amCode Received

<tbody>
</tbody>



And this is Sheet 2

DATEFACILITY_CODEFACILITY_NAMECLIENT_NAMECLIENT_PHONE_NOCLIENTIDREF_CODE_NUMBERCODE_TYPECODE_STATUSDECLINE_REASONPROBLEMCALLER_NAMECALLER_PHONEAPPROVERENTEREDBYENTRY_TIME

<tbody>
</tbody>
 
Last edited:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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