Poll

Aboot this blog

This blog is a cross between a scrapbook and a diary. I hope you find something here of interest. If you’d like to keep up with things as I see them then you can subscribe to my news feed by clicking the icon below:

feed-icon-28x28.png

DISCLAIMER

Please note that any reviews/recommendations are based solely on my own experience and does not constitute a guarantee that you will have the same experience. Please do your own research before parting with any money - the risk is all yours!

The Briticiser

Looking for my world famous US to GB English spelling converter?

THE “BRITICIZSER”

www.us2uk.eu

IT’S HERE

Tags

  Browsers     Flatpress         Tutorials     VBA     PHP     Hardware     Overcoming Disabilities     Bits and Bobs     Windows     Software     Reviews     News     Excel     Plausible Lies     Bookmarks     Odds and Sods     Education     Equipment     3D Stuff     Word     Family     Acrobat     Utilities  

Find me on Facebook

facebook-icon-28x28.pngVisit my Briticiser Facebook Page to keep up with things here - lots of reviews of stuff I’ve really used and also all kinds of helpful computer tips with a heavy bias towards SAVING YOU TIME.

How to return the text before or after the first or last space in an Excel cell value

Permalink: http://paul.us2uk.eu/?x=entry:entry160830-163100

If you ever run into a situation where you want to get rid of everything but the text before or after either the first or last space in a cell value then here’s a great formula to do just that.

The formula looks complicated but, as with all complicated Excel formulae, it all makes perfect sense once you break it down.

In this example the formula in Cell F4 returns the text after the last space in the value shown in Cell E4:

image

What this formula does is to replace each space in the value shown above in cell E4 with LOTS OF SPACES - it does this by counting how many characters are in the value (in this example there are 23 characters) and then SUBSTITUTE each individual space with that number of spaces. The REPT worksheet function REPeats Text a given number of times - in this case it is repeating one space 23 times, and the formula is then replacing each of the 3 spaces in the original text with those 23 spaces.

In the above example this would then give us a cell value which looks something like this:

image

Because we’ve spread it out like this we can now count 23 characters in from either the left or the right side of the cell (in this example we’ve used the RIGHT worksheet function to count backwards from the end of the cell value) and we will always end up with either leading or trailing spaces along with the text we’re trying to get, so all we do then is use the TRIM function to get rid of these spaces.

To swing this around and get the text with precedes the first space all you need to do is to use the LEFT worksheet function instead of RIGHT.

Support our caregiving

If you found anything on this site of use, interesting, or even mildly amusing then please consider dropping a few pennies in the jar to help us to take care of our disabled son who contracted encephalitis in 2007 at the age of 6 and who is now confined to a wheelchair. He is getting bigger as his mother and I get older, imagine that, please. Every penny we collect goes towards his upkeep, and towards his future care requirements.

Thank you.

Support our caregiving

Support our caregiving

If you found anything on this site of use, interesting, or even mildly amusing then please consider dropping a few pennies in the jar to help us to take care of our disabled son who contracted encephalitis in 2007 at the age of 6 and who is now confined to a wheelchair. He is getting bigger as his mother and I get older, imagine that, please. Every penny we collect goes towards his upkeep, and towards his future care requirements.

Thank you.