Extrapolate Text within parenthesis while adjusting header lines, create time range

[Livin404]

Greetings, I have a Column I would like to adjust. Please refer to photo. In Column A whenever there is a date I only need that cell to shift up one (all the rows will have to move up together) as not to miss-align the rows. However I would like the date in its new row to show the day of the week, month, day of month followed by year. In the image for example for "A1" it would read "THURSDAY NOVEMBER 12 2020". In place where the date was I would need "ORIGIN" Regarding the parenthesis I have the following VBA which I got from an earlier post on "Mr. Excel". It removes brackets all right, but it removes everything that is not in a bracket. I only want it to work on places below where there are dates and to ignore blank rows. Thank you,

 Sub ExtractFids()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(ISNUMBER(FIND(""("",@)),TRIM(MID(LEFT(@,FIND("")"",@)-1),FIND(""("",@)+1,99)),@)", "@", Addr))
End Sub

 

[Rick Rothstein]

I have read your description and tried to coordinate it with the picture you posted... and failed. Can you show us an example of your original data and in a second example show us what that data should look like after being processed.

 

[Livin404]

I have read your description and tried to coordinate it with the picture you posted... and failed. Can you show us an example of your original data and in a second example show us what that data should look like after being processed.

Thank you for getting back to me. I hope this image helps. The image you see will happen on worksheet "Inbound FIDS" The top part is the before and the bottom is the end result.

 

[Rick Rothstein]

Is this correct... if Column F has the text "DO NOT POST", then the text in Column A is to be left as is, for all other cells below the date row you want only the text inside the parentheses to remain?

 

[Livin404]

Hi, I have another VBA when the "do not post" will get deleted along with the row that it is on. There will be no rounded brackets at all in Column A. I should have deleted that row. Sorry for the confusion.

 

[Livin404]

Is this correct... if Column F has the text "DO NOT POST", then the text in Column A is to be left as is, for all other cells below the date row you want only the text inside the parentheses to remain?

Hello, I'm not sure if my latest reply got through. There will be no parenthesis in any of the columns. I meant to have the "DO NOT POST" deleted. I have a VBA before the one your looking at is activated.

 

[Rick Rothstein]

Your posted example (the one with the source and desired result) shows parentheses in the original data... those are still there, correct?

 

[Livin404]

yes that is correct, as stated earlier in the second image row BHM should not be there. I have a VBA to delete that row. The rest of it is correct. Thank you

 

[Livin404]

Your posted example (the one with the source and desired result) shows parentheses in the original data... those are still there, correct?

Is there anything else you may need for clarity? Thank you.

 

[Rick Rothstein]

See if this macro does what you want...

Sub Livin404()
  Dim X As Long
  With Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants)
    For X = .Areas.Count To 1 Step -1
      If X > 1 Then .Areas(X)(1).EntireRow.Insert
      With .Areas(X)
        .Resize(1).Offset(-1).Value = .Value
        .Resize(1).Offset(-1).Replace "(*", Year(Now), xlPart, , , , False, False
        .Resize(1).Value = "Origin"
        .Offset(1).Replace "*(", ""
        .Offset(1).Replace ")*", ""
      End With
    Next
  End With
End Sub