Seperate Data In One Cell Across Multiple Cells

Steve_Smith_1123

New Member
Joined
May 6, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

Hope you're all well. Although I'm something of a Journeymen Excel guy, I don't remember how I used to do this anymore.

I have a column that contains information about a series of posts inside our help desk in an individual cell. So for example, if someone went in and updated the ticket there is some information about that update. If there is someone attached a photo, that is also added.

What I would like to be able to do (ideally through a formula, although If VBA is easier I'm game) is seperate out each of these individual updates so that they are all ended in separate cells at the end of the column. There would be TWO CELLS populated along the row of each ticket for each individual update.

  1. Update Posts on December 23rd, 2021
    1. Post One: Just the date when the post was made.
    2. Post Two: The complete body of the message/update that was entered.
  2. I would like these to be posted in cells in reverse chronological order. So the first cell would contain the date,time of the FIRST POST Made, and then the Second cell would contain the body of the message for JUST that post.
    1. The next set of posts would then contain the date/time of the second post entered, and then in the next cell would be entered the body of that update.
    2. So the updates would go from the bottom of the information in the cell, to the top of the information in the cell.
Below you will find an example of the data in the cell. Additionally, you will also find an attached snippit of data to work from. You will notice in the snippit I attached, in the first row of data, I made an example.

One thing I think you'll note that will be helpful. There are a lot of enters/tabs used in the posts themselves. I don't think they would be a good point of demarcation to differentiate between one post and the next. If I had to guess, I would say the date would be a good one. I think it'll be clear to you when you give it a look.

I know this is probably a doozy for some of you, but I wasn't able to muscle this one through and am eager to get this done. Thank you for taking the time to review my post.

Code:
06/07/2022 13:21:06 - Daniel Rosenbaum (Customer Update)
Hey Amber,

Thank you for letting us know. We will close out this ticket as it does sound like an issue with the new computers.

Thanks,
Daniel Rosenbaum
Support Engineer

06/07/2022 10:39:56 - Riley Cox (Customer Update)
received from: riley.cox@global.ntt

Hello, please see incident below that was escalated to x4042

End User First&Last: Miandra Matthew

Issue Description: Amazon Connect - audio issues, dropping calls

Remote into End User PC (Y/N)? yes
If unable to remote in, please document why?

Troubleshooting Steps:
- User reports audio issues and dropped calls with Amazon Connect since last week
- The user frequently experiences problems with audio (providers not being able to hear or understand the user) and dropped calls with no warning
- There are no clear errors, it is just these issues with audio and call drops
- The user also mentioned that several other coworkers were reporting similar issues recently, but it is unclear if these other issues are directly related
- User is using Chrome and tried clearing Chrome cache and cookies from all time, but the issue is still occurring
- Sending email to ttec@service-now.com<mailto:ttec@service-now.com>

Date & Time when incident stated to occur: 26MAY2022

# of User Impacted: 1

User ID(s) of those impacted: miandra.matthew@renown.org<mailto:miandra.matthew@renown.org>

Priority/Severity of issue: moderate, issue happens every other call and makes it very difficult to work with providers

Steps to Recreate / How issue was encountered: it happens when taking incoming calls, there is no clear pattern but it happens on about half of all calls according to the user where either the outgoing audio quality is poor or the calls just drop

CTR ID(s): n/a

CCP log file (must be attached for all incidents routed): taken to attach to email

Desire/Expected Results: fix the issue with call audio and dropped calls

Make sure to attach screen shot of any errors: no screenshots would be relevant as there are no visual indicators of the problem, it is all in the call quality and audio



---- Fields Below are Required for Dispatch ---

Contact 10-digit Phone: 407 627 9354

Alternate Contact Name (only for High/Critical Tickets): moderate

Can this contact be reached at the number provided? (If not, gather alternate phone): yes



Location Information:

* Physical Building Address: user is fully remote, does not know reporting location

* Building Nickname: user is fully remote, does not know reporting location

* Department Name: Customer Service

* Department Tower/Floor: user is fully remote, does not know reporting location

* Exact location in Department (ex: Nurses Station; Room #; POD Color): user is fully remote, does not know reporting location



Riley Cox
TSR, Integrated Operations Center | Managed Services | Americas

[A picture containing drawing, light  Description automatically generated]<https://www.secure-24.com/>

T: (775) 982-4042
E: Riley.Cox@Global.NTT<mailto:Riley.Cox@Global.NTT>
[cid:image002.png@01D87A73.A04713B0]<http://www.secure-24.com/>  [cid:image003.png@01D87A73.A04713B0] <https://twitter.com/secure_24>   [cid:image004.png@01D87A73.A04713B0] <https://www.linkedin.com/company/secure-24/>   [cid:image005.png@01D87A73.A04713B0] <https://www.facebook.com/Secure24Hosting/>   [cid:image006.png@01D87A73.A04713B0] <http://youtube.com/Secure24Hosting>

This communication and any attached files may contain information that is confidential or privileged. Any unauthorized review, use, disclosure or distribution is prohibited. If this communication has been received in error, please notify me and delete or destroy it immediately.



Demo.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1NumberTask typeStateAcknowledged TimeBusiness DurationComments and Work notesDuration (In Hours)EscalationInternal NotesLast Customer Update ByLast Customer Update by eLoy byLast Customer Update by eLoy timeLast Internal NotesLast Internal Notes ByMade SLARestoration StepsService CategoryCustomer UpdateTIME OF POSTBODY OF POSTTIME OF POSTBODY OF POST
2eINC1282352IncidentClosedTuesday, December 21, 20212334912/30/2021 06:20:50 - Joshua Williams (Internal Notes) closing 12/30/2021 06:20:39 - Joshua Williams (Internal Notes) resolving incident 12/30/2021 06:20:39 - Joshua Williams (Customer Update) resolving incident 12/30/2021 06:20:13 - Joshua Williams (Internal Notes) restoring incident 12/30/2021 06:20:13 - Joshua Williams (Customer Update) restoring incident 12/29/2021 06:58:30 - Joshua Williams (Customer Update) Hey Emily, Any update on the issue with Joyce? Please let me know if Joyce is still experiencing the issue or if you have any new information. Thanks, Josh Williams Support Engineer 12/29/2021 06:58:30 - Joshua Williams (Internal Notes) Client unresponsive - sent reminder 12/23/2021 06:46:14 - Joshua Williams (Internal Notes) responded to client 12/23/2021 06:46:14 - Joshua Williams (Customer Update) Good Morning Emily, So I highly suspect that the VPN is the issue. The next things to try to verify if the VPN is truly the problem would be to not use the VPN and see if AWS connect works normally again. It would also be good to know if Joyce has been able to use Connect at home in the past with her VPN? Did she recently get or update her VPN? I did some research just into Meraki Box and found that some people complain of similar issues with not being able to use AWS services. If everything works as normal once Joyce disconnects her Meraki Box or GlobalProtect then we know for sure that is the root problem. I could then see if I could find a work around allowing her to still use her VPN with AWS Services. Thanks, Josh Williams Support Engineer 12/22/2021 15:13:41 - Emily Comeaux (Customer Update) reply from: emily.comeaux@hometownhealth.com They use a Meraki Box, but I am not sure she is required to use that with Amazon. I think they do use Global Protect VPN. 12/22/2021 14:56:00 - Joshua Williams (Internal Notes) Responded to client 12/22/2021 14:56:00 - Joshua Williams (Customer Update) The endpoint test results were really helpful! A few more questions... Does Joyce use a VPN to login to do work on her home computer? I think we have narrowed down the problem to being a network issue for Joyce. Since this is only happening at home then there is likely something blocking AWS Connect when it tries to connect to incoming calls. Let me know if Joyce uses a VPN at home. I'll be searching for potential solutions now that we are getting down to the root problem. Thanks, Josh Williams Support Engineer 12/22/2021 13:27:02 - Emily Comeaux (Customer Update) reply from: emily.comeaux@hometownhealth.com Hi, this is the error link from this morning she ran from home. 12/22/2021 13:27:02 - Emily Comeaux (Internal Notes) Customer Attachment : File Name : image001.png Inserted By : system 12/22/2021 13:27:02 - Emily Comeaux (Internal Notes) Customer Attachment : File Name : Error message.PNG Inserted By : system 12/22/2021 12:36:03 - Joshua Williams (Internal Notes) Updated state 12/22/2021 12:35:31 - Joshua Williams (Customer Update) Ok that's good information. That will help a lot in determining the problem. Does she use the same computer in office as she does at home or a different one? Has she recently received her home computer? Also, just a reminder to send over the endpoint test when you're able to but it should be done on the computer that is having the issues. Endpoint test - https://d3ugyzdarmq5tb.cloudfront.net/ Thanks, Josh Williams Support Engineer 12/22/2021 12:35:31 - Joshua Williams (Internal Notes) Responded to client 12/22/2021 11:57:26 - Emily Comeaux (Customer Update) reply from: emily.comeaux@hometownhealth.com Hi, Joyce’s phone works in office, just not at home. 12/22/2021 10:48:36 - Deborah Schottman (Internal Notes) Adding Danielle To the watchlist. 12/22/2021 10:43:36 - Joshua Williams (Internal Notes) Offered to set up a meeting to help 12/22/2021 10:43:36 - Joshua Williams (Customer Update) Let me know if yall need help with anything. If needed I can hop on a zoom call. Thanks, Josh Williams Support Engineer 12/22/2021 09:47:59 - Emily Comeaux (Customer Update) reply from: emily.comeaux@hometownhealth.com Hi Joyce, please see highlighted in below. 12/22/2021 09:25:58 - Joshua Williams (Internal Notes) state updated 12/22/2021 09:25:38 - Joshua Williams (Customer Update) Awesome thanks Emily! I'll keep an eye out for the info. Thanks, Josh Williams Support Engineer 12/22/2021 09:25:38 - Joshua Williams (Internal Notes) Respond to client 12/22/2021 09:20:03 - Emily Comeaux (Customer Update) reply from: emily.comeaux@hometownhealth.com Hi, this agent is not new. =). She will not be in until 11:30 so I will have her run these when she comes in. Thanks Em 12/22/2021 08:40:12 - Joshua Williams (Internal Notes) updated ticket info 12/22/2021 08:38:32 - Deborah Schottman (Internal Notes) creating the incident. 12/22/2021 08:38:13 - Deborah Schottman (Internal Notes) Setting the Incident attributes. 12/22/2021 06:18:52 - Joshua Williams (Customer Update) Hey Emily, I'm looking into this issue. Hopefully we can get it resolved asap. A few questions: Is Joyce a new agent or has been an agent and the error just started occurring? Does the agent have issues logging into AWS or just can't take any calls once she has logged into AWS? Please run an endpoint test (link below) and send back the results. If you could also get a CCP log of one of the calls that would help make the resolution time faster. Endpoint test - https://d3ugyzdarmq5tb.cloudfront.net/ Thanks, Josh Williams Support Engineer 12/22/2021 06:18:52 - Joshua Williams (Internal Notes) Assigned to myself and asked client for more info 12/21/2021 20:53:44 - Steve Smith (Internal Notes) Case Acknowledged by TTEC Digital 30548.79Normal12/30/2021 06:20:50 - Joshua Williams (Internal Notes) closing 12/30/2021 06:20:39 - Joshua Williams (Internal Notes) resolving incident 12/30/2021 06:20:13 - Joshua Williams (Internal Notes) restoring incident 12/29/2021 06:58:30 - Joshua Williams (Internal Notes) Client unresponsive - sent reminder 12/23/2021 06:46:14 - Joshua Williams (Internal Notes) responded to client 12/22/2021 14:56:00 - Joshua Williams (Internal Notes) Responded to client 12/22/2021 13:27:02 - Emily Comeaux (Internal Notes) Customer Attachment : File Name : image001.png Inserted By : system 12/22/2021 13:27:02 - Emily Comeaux (Internal Notes) Customer Attachment : File Name : Error message.PNG Inserted By : system 12/22/2021 12:36:03 - Joshua Williams (Internal Notes) Updated state 12/22/2021 12:35:31 - Joshua Williams (Internal Notes) Responded to client 12/22/2021 10:48:36 - Deborah Schottman (Internal Notes) Adding Danielle To the watchlist. 12/22/2021 10:43:36 - Joshua Williams (Internal Notes) Offered to set up a meeting to help 12/22/2021 09:25:58 - Joshua Williams (Internal Notes) state updated 12/22/2021 09:25:38 - Joshua Williams (Internal Notes) Respond to client 12/22/2021 08:40:12 - Joshua Williams (Internal Notes) updated ticket info 12/22/2021 08:38:32 - Deborah Schottman (Internal Notes) creating the incident. 12/22/2021 08:38:13 - Deborah Schottman (Internal Notes) Setting the Incident attributes. 12/22/2021 06:18:52 - Joshua Williams (Internal Notes) Assigned to myself and asked client for more info 12/21/2021 20:53:44 - Steve Smith (Internal Notes) Case Acknowledged by TTEC Digital Joshua WilliamsJoshua WilliamsThursday, December 30, 2021closingJoshua WilliamsTRUEBypass home VPNAmazon Connect12/30/2021 06:20:39 - Joshua Williams (Customer Update) resolving incident 12/30/2021 06:20:13 - Joshua Williams (Customer Update) restoring incident 12/29/2021 06:58:30 - Joshua Williams (Customer Update) Hey Emily, Any update on the issue with Joyce? Please let me know if Joyce is still experiencing the issue or if you have any new information. Thanks, Josh Williams Support Engineer 12/23/2021 06:46:14 - Joshua Williams (Customer Update) Good Morning Emily, So I highly suspect that the VPN is the issue. The next things to try to verify if the VPN is truly the problem would be to not use the VPN and see if AWS connect works normally again. It would also be good to know if Joyce has been able to use Connect at home in the past with her VPN? Did she recently get or update her VPN? I did some research just into Meraki Box and found that some people complain of similar issues with not being able to use AWS services. If everything works as normal once Joyce disconnects her Meraki Box or GlobalProtect then we know for sure that is the root problem. I could then see if I could find a work around allowing her to still use her VPN with AWS Services. Thanks, Josh Williams Support Engineer 12/22/2021 15:13:41 - Emily Comeaux (Customer Update) reply from: emily.comeaux@hometownhealth.com They use a Meraki Box, but I am not sure she is required to use that with Amazon. I think they do use Global Protect VPN. 12/22/2021 14:56:00 - Joshua Williams (Customer Update) The endpoint test results were really helpful! A few more questions... Does Joyce use a VPN to login to do work on her home computer? I think we have narrowed down the problem to being a network issue for Joyce. Since this is only happening at home then there is likely something blocking AWS Connect when it tries to connect to incoming calls. Let me know if Joyce uses a VPN at home. I'll be searching for potential solutions now that we are getting down to the root problem. Thanks, Josh Williams Support Engineer 12/22/2021 13:27:02 - Emily Comeaux (Customer Update) reply from: emily.comeaux@hometownhealth.com Hi, this is the error link from this morning she ran from home. 12/22/2021 12:35:31 - Joshua Williams (Customer Update) Ok that's good information. That will help a lot in determining the problem. Does she use the same computer in office as she does at home or a different one? Has she recently received her home computer? Also, just a reminder to send over the endpoint test when you're able to but it should be done on the computer that is having the issues. Endpoint test - https://d3ugyzdarmq5tb.cloudfront.net/ Thanks, Josh Williams Support Engineer 12/22/2021 11:57:26 - Emily Comeaux (Customer Update) reply from: emily.comeaux@hometownhealth.com Hi, Joyce’s phone works in office, just not at home. 12/22/2021 10:43:36 - Joshua Williams (Customer Update) Let me know if yall need help with anything. If needed I can hop on a zoom call. Thanks, Josh Williams Support Engineer 12/22/2021 09:47:59 - Emily Comeaux (Customer Update) reply from: emily.comeaux@hometownhealth.com Hi Joyce, please see highlighted in below. 12/22/2021 09:25:38 - Joshua Williams (Customer Update) Awesome thanks Emily! I'll keep an eye out for the info. Thanks, Josh Williams Support Engineer 12/22/2021 09:20:03 - Emily Comeaux (Customer Update) reply from: emily.comeaux@hometownhealth.com Hi, this agent is not new. =). She will not be in until 11:30 so I will have her run these when she comes in. Thanks Em 12/22/2021 06:18:52 - Joshua Williams (Customer Update) Hey Emily, I'm looking into this issue. Hopefully we can get it resolved asap. A few questions: Is Joyce a new agent or has been an agent and the error just started occurring? Does the agent have issues logging into AWS or just can't take any calls once she has logged into AWS? Please run an endpoint test (link below) and send back the results. If you could also get a CCP log of one of the calls that would help make the resolution time faster. Endpoint test - https://d3ugyzdarmq5tb.cloudfront.net/ Thanks, Josh Williams Support Engineer 12/22/21 6:1812/22/2021 06:18:52 - Joshua Williams (Customer Update) Hey Emily, I'm looking into this issue. Hopefully we can get it resolved asap. A few questions: Is Joyce a new agent or has been an agent and the error just started occurring? Does the agent have issues logging into AWS or just can't take any calls once she has logged into AWS? Please run an endpoint test (link below) and send back the results. If you could also get a CCP log of one of the calls that would help make the resolution time faster. Endpoint test - https://d3ugyzdarmq5tb.cloudfront.net/ Thanks, Josh Williams Support Engineer12/22/21 9:2012/22/2021 09:20:03 - Emily Comeaux (Customer Update) reply from: emily.comeaux@hometownhealth.com Hi, this agent is not new. =). She will not be in until 11:30 so I will have her run these when she comes in. Thanks Em
3eINC1312845IncidentClosedMonday, March 7, 2022003/07/2022 13:08:27 - Steve Smith (Customer Update) Amber, Thanks a lot. We're working here now. Thanks, Steve 03/07/2022 13:08:27 - Steve Smith (Internal Notes) Closing Incident 03/07/2022 12:48:09 - Kaye Barredo (Internal Notes) Test ticket and assigning to Steve as instructed (reference last name on Watchlist and Customer Account name 03/07/2022 12:43:40 - Kaye Barredo (Internal Notes) Case Acknowledged by TTEC Digital 03/07/2022 12:11:50 - Amber Page (Internal Notes) Customer Attachment : File Name : image004.png Inserted By : system 03/07/2022 12:11:50 - Amber Page (Internal Notes) Customer Attachment : File Name : image003.png Inserted By : system 03/07/2022 12:11:50 - Amber Page (Internal Notes) Customer Attachment : File Name : image002.png Inserted By : system 03/07/2022 12:11:50 - Amber Page (Internal Notes) Customer Attachment : File Name : image006.png Inserted By : system 03/07/2022 12:11:50 - Amber Page (Internal Notes) Customer Attachment : File Name : Mimecast Large File Send Instructions.eml Inserted By : system 03/07/2022 12:11:49 - Amber Page (Internal Notes) Customer Attachment : File Name : image005.png Inserted By : system 03/07/2022 12:11:49 - Amber Page (Customer Update) received from: amber.page@global.ntt I'm using Mimecast to share large files with you. Please see the attached instructions. This is a test ticket. Steve knows it is coming through. Please assign to Steve Summary: Date & Time when incident stated to occur: # of User Impacted: User ID(s) of those impacted: Priority/Severity of issue: Steps to Recreate / How issue was encountered: CTR ID(s): CCP log file: Desire/Expected Results: Make sure to attach screen shot of any errors: ---- Fields Below are Required for Dispatch --- Contact 10-digit Phone: Alternate Contact Name (only for High/Critical Tickets): Can this contact be reached at the number provided? (If not, gather alternate phone): Location Information: * Physical Building Address: * Building Nickname: * Department Name: * Department Tower/Floor: * Exact location in Department (ex: Nurses Station; Room #; POD Color): Amber Page Manager, Integrated Operations Center | Managed Services | Americas [https://www.secure-24.com/] M: (770) 543-8087 E: Amber.Page@global.ntt [mailto:Amber.Page@global.ntt] [http://www.secure-24.com/] [https://twitter.com/secure_24] [https://www.linkedin.com/company/secure-24/] [https://www.facebook.com/Secure24Hosting/] [http://youtube.com/Secure24Hosting] This communication and any attached files may contain information that is confidential or privileged. Any unauthorized review, use, disclosure or distribution is prohibited. If this communication has been received in error, please notify me and delete or destroy it immediately. 03/07/2022 12:11:49 - Amber Page (Internal Notes) Customer Attachment : File Name : image001.png Inserted By : system 141.63Normal03/07/2022 13:08:27 - Steve Smith (Internal Notes) Closing Incident 03/07/2022 12:48:09 - Kaye Barredo (Internal Notes) Test ticket and assigning to Steve as instructed (reference last name on Watchlist and Customer Account name 03/07/2022 12:43:40 - Kaye Barredo (Internal Notes) Case Acknowledged by TTEC Digital 03/07/2022 12:11:50 - Amber Page (Internal Notes) Customer Attachment : File Name : image004.png Inserted By : system 03/07/2022 12:11:50 - Amber Page (Internal Notes) Customer Attachment : File Name : image003.png Inserted By : system 03/07/2022 12:11:50 - Amber Page (Internal Notes) Customer Attachment : File Name : image002.png Inserted By : system 03/07/2022 12:11:50 - Amber Page (Internal Notes) Customer Attachment : File Name : image006.png Inserted By : system 03/07/2022 12:11:50 - Amber Page (Internal Notes) Customer Attachment : File Name : Mimecast Large File Send Instructions.eml Inserted By : system 03/07/2022 12:11:49 - Amber Page (Internal Notes) Customer Attachment : File Name : image005.png Inserted By : system 03/07/2022 12:11:49 - Amber Page (Internal Notes) Customer Attachment : File Name : image001.png Inserted By : system Steve SmithSteve SmithMonday, March 7, 2022Closing IncidentSteve SmithTRUECase Cancelled by UI actionAdmin03/07/2022 13:08:27 - Steve Smith (Customer Update) Amber, Thanks a lot. We're working here now. Thanks, Steve 03/07/2022 12:11:49 - Amber Page (Customer Update) received from: amber.page@global.ntt I'm using Mimecast to share large files with you. Please see the attached instructions. This is a test ticket. Steve knows it is coming through. Please assign to Steve Summary: Date & Time when incident stated to occur: # of User Impacted: User ID(s) of those impacted: Priority/Severity of issue: Steps to Recreate / How issue was encountered: CTR ID(s): CCP log file: Desire/Expected Results: Make sure to attach screen shot of any errors: ---- Fields Below are Required for Dispatch --- Contact 10-digit Phone: Alternate Contact Name (only for High/Critical Tickets): Can this contact be reached at the number provided? (If not, gather alternate phone): Location Information: * Physical Building Address: * Building Nickname: * Department Name: * Department Tower/Floor: * Exact location in Department (ex: Nurses Station; Room #; POD Color): Amber Page Manager, Integrated Operations Center | Managed Services | Americas [https://www.secure-24.com/] M: (770) 543-8087 E: Amber.Page@global.ntt [mailto:Amber.Page@global.ntt] [http://www.secure-24.com/] [https://twitter.com/secure_24] [https://www.linkedin.com/company/secure-24/] [https://www.facebook.com/Secure24Hosting/] [http://youtube.com/Secure24Hosting] This communication and any attached files may contain information that is confidential or privileged. Any unauthorized review, use, disclosure or distribution is prohibited. If this communication has been received in error, please notify me and delete or destroy it immediately. Hey Emily,1
Outcome Example
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Steve_Smith_1123

New Member
Joined
May 6, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Instead of:
  1. The next set of posts would then contain the date/time of the second post entered, and then in the next cell would be entered the body of that update.
I should have said:
  1. The next set of cells would then contain...
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,169
Office Version
  1. 365
Platform
  1. Windows
Not sure how you would do it in a formula, possibly using a lambda but see if the code below works for you.

I am assuming that
• your sheet with the data on it is the active sheet
• that the original data has all the columns up to and including column R populated and from S on are empty
• that column R has the Customer Update that you want split out into additional column in the format Date then Body Text repeatedly until your run out of dates
and in ascending date order from left to right.

Use a copy of your workbook to run it on and put it in a Standard Module in VBA.

VBA Code:
Sub SplitCustomerUpdate()

    Dim wsCustUpd As Worksheet
    Dim rngCustUpd As Range
    Dim arrCustUpd As Variant, arrSplit As Variant, arrOut As Variant
    Dim lrCustUpd As Long, i As Long, j As Long, colOut As Long, colOutMax As Long
    Dim outRecord As String


    Set wsCustUpd = ActiveSheet
    With wsCustUpd
        lrCustUpd = .Cells(Rows.Count, "R").End(xlUp).Row
        Set rngCustUpd = .Range("R2:R" & lrCustUpd)
        arrCustUpd = rngCustUpd
    End With

    ReDim arrOut(1 To UBound(arrCustUpd), 1 To 100)
    For i = 1 To UBound(arrCustUpd)
        outRecord = ""
        colOut = 0
        arrSplit = Split(arrCustUpd(i, 1), vbLf)
        For j = UBound(arrSplit) To 0 Step -1
            If IsDate(Left(arrSplit(j), 10)) Then
                'Debug.Print arrSplit(j)
                colOut = colOut + 1
                    arrOut(i, colOut) = Left(arrSplit(j), 19)
                colOut = colOut + 1
                    arrOut(i, colOut) = Replace(arrSplit(j) & vbLf & outRecord, Left(arrSplit(j), 19) & " - ", "")
                    outRecord = ""
            Else
                outRecord = arrSplit(j) & vbLf & outRecord
            End If
        Next j
        If colOut > colOutMax Then colOutMax = colOut
    Next i
    
    rngCustUpd.Offset(0, 1).Resize(, colOutMax).Value = arrOut
    rngCustUpd.Offset(0, 1).Resize(, colOutMax).Columns.AutoFit
    
    rngCustUpd.Offset(-1, 1).Resize(1, 2) = Array("TIME OF POST", "BODY OF POST")
    rngCustUpd.Offset(-1, 1).Resize(1, 2).Copy Destination:=rngCustUpd.Offset(-1, 1).Resize(1, colOutMax)

    For j = 2 To colOutMax Step 2
        rngCustUpd.EntireColumn.Copy
            rngCustUpd.Offset(0, j).EntireColumn.PasteSpecial Paste:=xlPasteColumnWidths
    Next j
End Sub
 
Solution

Forum statistics

Threads
1,176,670
Messages
5,904,378
Members
435,088
Latest member
Kingsof82

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
Top