Change the position of text in the cell

Z

Zain_inout

New Member
Joined
Sep 8, 2021
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I am working on a macro that will translate the French part to English. The report has a different date every month so I am only able to write a general code that will translate the month name.

For example, the cell contains "December 27, 2020 to October 2, 2021". To translate this part to French, I have written the following code:

ActiveSheet.Range("A3").Select
Selection.Replace What:=" to", Replacement:=" au", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

ActiveSheet.Range("A1").Select

ActiveSheet.Range("A3").Select
Selection.Replace What:="January", Replacement:="janvier", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

ActiveSheet.Range("A3").Select
Selection.Replace What:="February", Replacement:="février", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

ActiveSheet.Range("A3").Select
Selection.Replace What:="March", Replacement:="mars", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Range("A3").Select
Selection.Replace What:="April", Replacement:="avril", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Range("A3").Select
Selection.Replace What:="May", Replacement:="mai", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Range("A3").Select
Selection.Replace What:="June", Replacement:="juin", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Range("A3").Select
Selection.Replace What:="July", Replacement:="juillet", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Range("A3").Select
Selection.Replace What:="August", Replacement:="août", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Range("A3").Select
Selection.Replace What:="September", Replacement:="septembre", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Range("A3").Select
Selection.Replace What:="October", Replacement:="octobre", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Range("A3").Select
Selection.Replace What:="November", Replacement:="novembre", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Range("A3").Select
Selection.Replace What:="December", Replacement:="décembre", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

When the code is run, I get the following output:
"decembre 27, 2020 au octobre 2, 2021".

Now, my requirement is to have the final result as follows:
"decembre 27, 2020 au octobre 2, 2021".
Basically just interchanging the day and the month position in the cell.

Any suggestion/help is much appreciated.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Assuming you meant to say you want the output to look like "27 decembre 2020 au 2 octobre 2021" try:

VBA Code: 
Option Explicit

Function TranslateDates(rng As Range) As String
' This function returns a translated and reformatted phrase from the input range rng containing an English phrase in the form of
' Mmmm d, yyyy to Mmmm, d, yyyy example "January 12, 2020 to February 7 2021"  to a French phrase in the form of
' d mmmm yyyy au d mmmm yyyy example "12 janvier 2020 au 7 février 2021

' This part dimensions all of the variables used in the function.  English and French default to variant to hold translation arrays
    Dim R As String, English, French, i As Long, c1 As Long, c2 As Long, d As String
    
' This part loads the initial variables
    English = Array(" to", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
    French = Array(" au", "janvier", "février", "mars", "avril", "mai", "juin", "juillet", "août", "septembre", "octobre", "novembre", "decembre")
    R = rng
    
' This part moves the day of month before the month name in variable R using helper variables c1, c2, d
    c1 = InStr(1, R, ",")
    c2 = InStr(1, R, " ")
    d = Mid(R, c2 + 1, c1 - c2 - 1) & " "
    R = d & Left(R, c2 - 1) & Right(R, Len(R) - c1)
    c1 = InStr(1, R, ",")
    c2 = InStr(c1 - 4, R, " ")
    d = Mid(R, c2 + 1, c1 - c2 - 1) & " "
    R = Replace(Left(R, c2 - 1) & Right(R, Len(R) - c1), "to ", "to " & d)
    
'This part translates the word " to" and month names in R from English to French
    For i = 0 To 12
        If InStr(1, R, English(i)) Then R = Replace(R, English(i), French(i))
    Next
    
' This part assigns the translated/reformatted string to the function output
    TranslateDates = R
End Function

Sub TranslateA3()
'This subroutine overwites cell A3 with a translated/reformated version of itself calculated in UDF TranslateDates
    Range("A3") = TranslateDates(Range("A3"))
End Sub
 
Upvote 0
JGordon11 said:
Assuming you meant to say you want the output to look like "27 decembre 2020 au 2 octobre 2021" try:

VBA Code: 
Option Explicit

Function TranslateDates(rng As Range) As String
' This function returns a translated and reformatted phrase from the input range rng containing an English phrase in the form of
' Mmmm d, yyyy to Mmmm, d, yyyy example "January 12, 2020 to February 7 2021"  to a French phrase in the form of
' d mmmm yyyy au d mmmm yyyy example "12 janvier 2020 au 7 février 2021

' This part dimensions all of the variables used in the function.  English and French default to variant to hold translation arrays
    Dim R As String, English, French, i As Long, c1 As Long, c2 As Long, d As String
   
' This part loads the initial variables
    English = Array(" to", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
    French = Array(" au", "janvier", "février", "mars", "avril", "mai", "juin", "juillet", "août", "septembre", "octobre", "novembre", "decembre")
    R = rng
   
' This part moves the day of month before the month name in variable R using helper variables c1, c2, d
    c1 = InStr(1, R, ",")
    c2 = InStr(1, R, " ")
    d = Mid(R, c2 + 1, c1 - c2 - 1) & " "
    R = d & Left(R, c2 - 1) & Right(R, Len(R) - c1)
    c1 = InStr(1, R, ",")
    c2 = InStr(c1 - 4, R, " ")
    d = Mid(R, c2 + 1, c1 - c2 - 1) & " "
    R = Replace(Left(R, c2 - 1) & Right(R, Len(R) - c1), "to ", "to " & d)
   
'This part translates the word " to" and month names in R from English to French
    For i = 0 To 12
        If InStr(1, R, English(i)) Then R = Replace(R, English(i), French(i))
    Next
   
' This part assigns the translated/reformatted string to the function output
    TranslateDates = R
End Function

Sub TranslateA3()
'This subroutine overwites cell A3 with a translated/reformated version of itself calculated in UDF TranslateDates
    Range("A3") = TranslateDates(Range("A3"))
End Sub
Click to expand...
Hi Gordon,

Thanks for your reply. I am looking to get the output as
Now, my requirement is to have the final result as follows:
"decembre 27, 2020 au octobre 2, 2021".
 
Upvote 0
VBA Code: 
Option Explicit

Function TranslateDates(rng As Range) As String
' This function returns a translated and reformatted phrase from the input range rng containing an English phrase in the form of
' Mmmm d, yyyy to Mmmm, d, yyyy example "January 12, 2020 to February 7 2021"  to a French phrase in the form of
' d mmmm yyyy au d mmmm yyyy example "12 janvier 2020 au 7 février 2021

' This part dimensions all of the variables used in the function.  English and French default to variant to hold translation arrays
    Dim R As String, English, French, i As Long
    
' This part loads the initial variables
    English = Array(" to", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
    French = Array(" au", "janvier", "février", "mars", "avril", "mai", "juin", "juillet", "août", "septembre", "octobre", "novembre", "decembre")
    R = rng
    
'This part translates the word " to" and month names in R from English to French
    For i = 0 To 12
        If InStr(1, R, English(i)) Then R = Replace(R, English(i), French(i))
    Next
    
' This part assigns the translated/reformatted string to the function output
    TranslateDates = R
End Function

VBA Code: 
Sub TranslateA3()
'This subroutine overwites cell A3 with a translated/reformated version of itself calculated in UDF TranslateDates
    Range("A3") = TranslateDates(Range("A3"))
End Sub
 
Upvote 0
Hi Gordon,

I really appreciate your help! Somehow I am getting an error because of "Function". Below is a screenshot.:

1635334883533.png


Can you help me just write a code that changes "December 27, 2020 to October 2, 2021" to "27 December, 2020 to 2 October , 2021"??
Please note that the month, date and year can be different in different reports.
 
Upvote 0
either delete the Option Explicit or move it to top of the module.

VBA Code: 
Function FormatDates(rng As Range) As String
' This function returns a reformatted phrase from the input range rng containing an English phrase in the form of
' Mmmm d, yyyy to Mmmm, d, yyyy example "January 12, 2020 to February 7 2021"  to a phrase in the form of
' d mmmm yyyy au d mmmm yyyy example "12 January 2020 to 7 February 2021

' This part dimensions all of the variables used in the function.
    Dim R As String, i As Long, c1 As Long, c2 As Long, d As String
    
' This part loads the initial variable
    R = rng
    
' This part moves the day of month before the month name in variable R using helper variables c1, c2, d
    c1 = InStr(1, R, ",")
    c2 = InStr(1, R, " ")
    d = Mid(R, c2 + 1, c1 - c2 - 1) & " "
    R = d & Left(R, c2 - 1) & Right(R, Len(R) - c1)
    c1 = InStr(1, R, ",")
    c2 = InStr(c1 - 4, R, " ")
    d = Mid(R, c2 + 1, c1 - c2 - 1) & " "
    R = Replace(Left(R, c2 - 1) & Right(R, Len(R) - c1), "to ", "to " & d)
    
' This part assigns the reformatted string to the function output
    FormatDates = R
End Function

Sub FormatA3()
'This subroutine overwites cell A3 with a reformated version of itself calculated in UDF FormatDates
    Range("A3") = FormatDates(Range("A3"))
End Sub
 
Upvote 0
JGordon11 said:
either delete the Option Explicit or move it to top of the module.

VBA Code: 
Function FormatDates(rng As Range) As String
' This function returns a reformatted phrase from the input range rng containing an English phrase in the form of
' Mmmm d, yyyy to Mmmm, d, yyyy example "January 12, 2020 to February 7 2021"  to a phrase in the form of
' d mmmm yyyy au d mmmm yyyy example "12 January 2020 to 7 February 2021

' This part dimensions all of the variables used in the function.
    Dim R As String, i As Long, c1 As Long, c2 As Long, d As String
   
' This part loads the initial variable
    R = rng
   
' This part moves the day of month before the month name in variable R using helper variables c1, c2, d
    c1 = InStr(1, R, ",")
    c2 = InStr(1, R, " ")
    d = Mid(R, c2 + 1, c1 - c2 - 1) & " "
    R = d & Left(R, c2 - 1) & Right(R, Len(R) - c1)
    c1 = InStr(1, R, ",")
    c2 = InStr(c1 - 4, R, " ")
    d = Mid(R, c2 + 1, c1 - c2 - 1) & " "
    R = Replace(Left(R, c2 - 1) & Right(R, Len(R) - c1), "to ", "to " & d)
   
' This part assigns the reformatted string to the function output
    FormatDates = R
End Function

Sub FormatA3()
'This subroutine overwites cell A3 with a reformated version of itself calculated in UDF FormatDates
    Range("A3") = FormatDates(Range("A3"))
End Sub
Click to expand...
Hi Gordon,

Thanks a lot for this. I will try this and let you know
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Clean Text - Remove Repeat Line Breaks
Replies
4
Views
196
lneidorf
L
M
  • Question
Saveas newwb, close new wb, delete old wb
Replies
8
Views
198
eiloken
E
S
  • Question
Parse data - Overwrite lines with updated data
Replies
0
Views
129
Scifo
S
F
  • Question
find and replace only within selection
Replies
3
Views
503
Fluff
Fluff
R
  • Question
Replace links after moving worksheets from one workbook to another.
Replies
2
Views
125
RockandGrohl
R

Forum statistics

Threads
1,215,193
Messages
6,123,560
Members
449,108
Latest member
rache47

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