Insert space between month and year sting

[JRed1]

Hi all, and Happy New Year!

Been unsuccessful with finding a solution to add a space between the month and year in a date string.
The initial date string is 06 Jan2024, and trying to change to 06 Jan 2024.

The date column is E, the dates start in E2, and there are no blank cells in the column.
A macro was developed to format several different reports, so the amount of rows vary depending on the report.

Any help is appreciated, and Thank You in advance!

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Dates", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Dates.1", "Dates.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"Dates.1", "Dates.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Book2
	E	F	G
1	Dates		Merged
2	06 Jan2024		06 Jan 2024
3	7 Jan2024		7 Jan 2024
4	8 Jan2024		8 Jan 2024
5	9 Jan2024		9 Jan 2024
6	10 Jan2024		10 Jan 2024
Sheet1

 

[Scott Huish]

You could do a Text to Columns, and when you get to Date, select DMY and format as dd mmm yyyy

In a macro:

Sub Macro1()
Range("A:A").TextToColumns Destination:=Range("A1"), FieldInfo:=Array(1, 4)
Range("A:A").NumberFormat = "dd mmm yyyy"
End Sub

 

[Peter_SSs]

If they are text values and you want them to stay that way you could try this

Sub Add_Space()
  With Range("E2", Range("E" & Rows.Count).End(xlUp))
    .Value = Evaluate("replace(" & .Address & ",7,0,"" "")")
  End With
End Sub

If they are text values and you want to convert them into actual dates, another option to try would be

Sub Add_Space_v2()
  With Range("E2", Range("E" & Rows.Count).End(xlUp))
    .NumberFormat = "dd mmm yyyy"
    .Value = .Value
  End With
End Sub

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[JRed1]

Thank you all for the replies! After reviewing the report, my original description was incorrect. There is a line break (Alt+Return) in the date string after the month. A space was not needed, just needed to remove the line break. This was the fix:

WS.Range("E2", Range("E" & Rows.Count).End(xlUp)).Select
    For Each cell In Selection
        cell.Value = Replace(cell.Value, Chr(10), " ")
        Next
    Selection.NumberFormat = "[$-en-US]dd-mmm-yyyyy;@"

 

[Peter_SSs]

Thanks for the update. You should still be able to process the whole column at once rather than cycling through one cell at a time.
Also, 'selecting' in vba code is rarely required and tends to make your code slower & flicker. You could try ..

With WS.Range("E2", WS.Range("E" & Rows.Count).End(xlUp))
  .NumberFormat = "[$-en-US]dd-mmm-yyyyy;@"
  .Value = Evaluate("substitute(" & .Address & ",char(10),"""")")
End With

 

[JRed1]

Hi Peter. Thank you so much for the feedback! For a lack of a better explanation (on my part) with this code applied, column "E" resulted with all the same dates from E2. Basically, copied from E2 and pasted to the remaining filled cells in the E column. The dates are varied in the original data in column E.

Not to be disrespectful, the run time of using "select" is a split second for over a thousand rows of different dates within the generated reports.

Is there a more logical solution? I want to work with the experts to gain a better understanding of code.

Sincerely, John

 

[Peter_SSs]

with this code applied, column "E" resulted with all the same dates from E2. Basically, copied from E2 and pasted to the remaining filled cells in the E column.

That does not happen for me.

Here is my full code. Based on your partial code above, I have assumed the code is acting on the Active Sheet.
Is that the case?
Is your code in a standard module or a worksheet or workbook module?

Sub Add_Space_v3()
  Dim WS As Worksheet
  Set WS = ActiveSheet
  
  With WS.Range("E2", WS.Range("E" & Rows.Count).End(xlUp))
    .NumberFormat = "[$-en-US]dd-mmm-yyyyy;@"
    .Value = Evaluate("substitute(" & .Address & ",char(10),"""")")
  End W

Test worksheet before code (XL2BB) automatically wraps the text because of the CHAR(10):

JRed1.xlsm
	E
1	Date
2	06 Jan 2024
3	25 Feb 2023
4	21 Dec 2000
5
Sheet3

After code:

JRed1.xlsm
	E
1	Date
2	06-Jan-2024
3	25-Feb-2023
4	21-Dec-2000
5
Sheet3

Not to be disrespectful, the run time of using "select" is a split second for over a thousand rows of different dates within the generated reports.

My comment was a general comment about selecting in code and without knowing how big your data is. In any case, I would recommend that you try to develop your coding to not use 'Select' wherever possible.

 

[Fluff]

with this code applied, column "E" resulted with all the same dates from E2.

Were you using 365 or 2019 when this happened?