Apr 24 2017 04:54 AM
Apr 24 2017 07:42 AM
Apr 25 2017 05:26 PM
Oliver's solution is great where one-off transformations are required, which may apply to many situations. If you wish to automate the process, so it can be resused in the future, you can use a combination of FIND, LEFT, RIGHT and CONCATENTATE functions. Please see the attached file for a possible solution. Alternatively, you can use Power Query ('Get & Transform' in Excel 2016) to automate the transformations.
Apr 26 2017 04:17 AM
Okay, one more way to reverse the sequence of words in the string is to use VBA, especially taking into account the reverse is to be done in the same cell.
Lot of samples exists. Code below works if stay on the cell and run that macro
Sub ReverseWords() Dim InputStr As String Dim ResultStr As String Dim InputArray() As String Dim OutputStr As String Dim SeparatorStr As String Dim WordsCount As Integer Dim WorkRng As Range SeparatorStr = "-" 'Take string from selected cell Set WorkRng = Application.Selection InputStr = WorkRng.Value 'Split and put into an array InputArray = Split(InputStr, SeparatorStr) WordsCount = UBound(InputArray) 'Combine all the words in reverse ResultStr = "" xSep = "" For i = WordsCount To 0 Step -1 If i = 0 Then xSep = "" Else: xSep = "-" End If ResultStr = ResultStr & InputArray(i) & xSep Next WorkRng.Value = ResultStr End Sub
I believe it could be expanded on the case when range of cells is selected to reverse words in each of it.
Apr 27 2017 11:31 PM
body { font-family:arial; font-size:12pt; color:navy } body .code { font:mono-spaced; font-size:12pt; color:navy; } body .comment { font-family:mono-spaced; font-size:12pt; color:green; }
Here's some very simple code to do it.
Sub ReverseList() Dim SS() As String Dim T As String Dim N As Long On Error GoTo ErrH: SS = Split(ActiveCell, "-") Application.EnableEvents = False For N = UBound(SS) To LBound(SS) Step -1 T = T + SS(N) + "-" Next N T = Left(T, Len(T) - 1) ActiveCell.Value = T ErrH: Application.EnableEvents = True Debug.Print T End Sub
Apr 27 2017 11:33 PM
Here's some very simple code to do it.
Sub ReverseList() Dim SS() As String Dim T As String Dim N As Long On Error GoTo ErrH: SS = Split(ActiveCell, "-") Application.EnableEvents = False For N = UBound(SS) To LBound(SS) Step -1 T = T + SS(N) + "-" Next N T = Left(T, Len(T) - 1) ActiveCell.Value = T ErrH: Application.EnableEvents = True Debug.Print T End Sub