today let's reverse a piece of text string in google sheets and excel so it's gonna be pretty
much the same formula for both so i'm gonna start in excel because it's easier to zoom in here let's
start by going over the logic how this is going to actually happen so if you look here we have this
florida oranges here so generally if you just do this function equals mid point to this text do a
comma then i'm gonna have a start number so let's say i'm gonna do one and then i'm gonna do comma
and then number of characters again one so right now if i close parentheses and hit
enter you'll see i get an f and the reason for that is because we said to start at number one
and to also extract just one character number of characters so that gets us the first character
now if i change this starting character to 2 see i get an l because now we get the second character
and one character after that now if i go on the side here and type something like one two three
four and then go back to this and instead of this two erase the two and just select this range of
one two three four as a starting number instead of a single number now i give it a range of numbers
i hit enter see i get the first four characters from this text now if i go four three two one
then i'm gonna get them the other way around right starting from r o l f so basically if i could
just start from this last number whatever this s is and then keep going backwards until the first
character that should get me something like this so how can i get a range of numbers like that so
i'm going to go ahead and erase this for a second and we'll come back to that formula
here to update this range in just a second for now let's just do one
so what i'm going to do here separately i'm going to use this sequence function
and for sequence function we have number of rows number of columns starting number and step if i
did something like 10 as number of rows comma and then number of columns i do one close parenthesis
and hit enter i get from 1 to 10. so then if i do another comma and then as a starting number
instead of starting from 1 which what it does by default i change it to let's say 25
then it starts from 25 and keeps going 10 down and then if i go after this last
character 25 after the comma i can also give it a step and this step could also be negative
so if i go negative 1 as a step we'll start from 25 c and we'll basically go back
down and if i do 25 25 to basically have 25 rows and to start from 25th number
then i'm gonna go from 25 up to one so instead of 25 what i can do i can just calculate
the length of our string right here using len function and see it tells us that this
is 15 characters long so for this particular case for florida oranges we actually need this to be
15 and 15 to get from 15 to 1 and then based on that if i go and erase this one and highlight this
range from 15 to 1. if i hit enter see we get florida oranges backwards like this so florida
oranges now i'm going to combine all of these in a single formula and the way i can do that
instead of hard-coding this 15s here we can go back and copy this lend function hit escape
go back to this and instead of this 15 i'm gonna paste that instead of this 15 i'm going to paste
that same land function and hit enter and now we have a dynamic way of calculating how many
characters it is i don't need this anymore and then instead of here referring to this
range right there i'm simply going to just select this whole sequence function without equal sign
copy that hit escape go back to this and paste that right here instead of that range
now finally if i hit enter that should give me this and i wouldn't need this and now we have a
single formula here that gets us these characters backboard so now to just have one piece of string
in excel i could just do can cat function like this around this whole thing
open and close here so you can see how i got that backboard but this concat function in
google sheets doesn't do the same thing in google sheets can cat function doesn't handle ranges
like it does in excel so to make it compatible for both instead of concat let me actually remove this
parenthesis and go back to my mid function i'm going to go back and instead of that i'm
going to use this function that's available in both which is called text join now text join
is going to have a delimiter so in this case i don't want any delimiter so i'm going to
do double quotes just an empty string comma and then ignore empty cells we don't want to ignore
anything so we'll just say false comma and finally the last argument here is the range
or text which is going to be this so i'm going to go ahead and close
parentheses after this whole thing hit enter and now that should produce the same results
as our concat would now that works pretty well already but if i keep dragging this down a little
more if i have empties i'm gonna get this error so i probably just want to leave it blank so for
that we'll just add a wrapper for this we'll go after this whole thing and we'll say if
that a1 equals to blank comma blank then leave it blank comma otherwise if it's not blank we'll do
this reverse thing i'll close parentheses and hit enter and then we should be able to drag this down
so we got our florida oranges backwards and so on so this same formula now should work
in google sheets if we just add array formula for this so i'm just going to copy this hit escape
go back to google sheets just to show you that and paste it here
same exact formula the only thing i'm gonna do i'm gonna do ctrl shift enter or command
shift enter if you're on a mac to get array formula around this whole thing hit enter
see it auto fills it for me good enough and if i fill it after it still works just fine
and there you go that's our reverse string thanks for watching and i'll see in the next video