Excel 2016. Trying to avoid formula way. Menu options? Google'd, but could make work. Thanks.
Excel 2016. Trying to avoid formula way. Menu options? Google'd, but could make work. Thanks.
Type them in backwards?
VBA
It will be a combo of Len, Righ, Left and concatenate formulas + boolean logic (to automate).
You will need to know the length of each word. Automate the amount to move Right on each word - and then automate how far to move back Left as you move across the word. Finally, you will want to Concatenate the letters.
Something like this:
=iferror(left(right($B4,E$1),D$1),concatenate(A4,B4,C4,D4))
In this B4 is your word. E1 and D1 are cells that have automated counts telling Excel where to cut your words.
This is all easy if your words are the same length. It gets harder when you have variable word lengths.
Hope this helps.
try this? wrote:
VBA
Yes, for some stupid reason it's not a built in function. You have to use VBA and store it in the PERSONAL.xlsb workbook for later use.
Function Reversestr(str As String) As String
Reversestr = StrReverse(Trim(str))
End Function
To use the formula on cell A1, the formula would be.
=PERSONAL.XLSB!Reversestr(A1)
I am trying to picture this. What are your starting with and wanting to end with?
The question is imprecise.
Is string length and/or number of words predictable or bounded? If so, what are those defining parameters?
Are you looking to reverse word order in a sentence, reverse the entire string, or reverse words while preserving order, or?
Example: If the input cell were "letsrun mods are no fun" would desired output be:
a) "fun no are mods letsrun", or
b) "nuf on era sdom nurstel", or
c) "nurstel sdom era on nuf", or
d) ?
If there isnt a huge amount of words in each cell then here is an easy way:
Use text to columns to separate all words into their own column, then use concatenate (super simple formula to learn if you've never used it) to re-append them in reverse order.
Ernest wrote:
The question is imprecise.
Is string length and/or number of words predictable or bounded? If so, what are those defining parameters?
Are you looking to reverse word order in a sentence, reverse the entire string, or reverse words while preserving order, or?
Example: If the input cell were "letsrun mods are no fun" would desired output be:
a) "fun no are mods letsrun", or
b) "nuf on era sdom nurstel", or
c) "nurstel sdom era on nuf", or
d) ?
a) "fun no are mods letsrun".
If number of words in the input string is predictable or reasonably finite, a single-cell Excel formula can be written to accomplish reversal. This involves nested string manipulation and parsing, using FIND, LEN, MID, RIGHT, LEFT and "&" for concatonation. A computational scratch area for multiple formulae makes this is simpler.
Anyway you slice it, this is messy and increasingly unweildly using native Excel functions.
To perform word re-ordering for an input string with a large and/or uncertain number of words, you need to use a conditional loop - and that requires a custom VBA function (which Excel supports).
Google is your friend:
https://www.vitoshacademy.com/vba-reverse-words-in-a-sentence/