Once again i'm speechless. The quality of explanation, including a workbook to test everything, is outstanding.
Leila, you have no idea how long i have suffered from this spacing issue specially with numbers! you are going straight to heaven for making our life easier :) THANKS A LOT. i have been following your channel & becomeing a huge fan.
Thank you so much. Your tutorials are so useful, very easy to follow and understand.
You can also use the CLEAN function in combination with TRIM to get rid of the non-printable characters that appear to be spaces. EDIT - after seeing another comment, I should clarify that it seems CLEAN will not work for CHAR(160) but it does work for many others.
Wow! Just Wow! Thank you for being such a marvellous human being Leila. After struggling all week to figure this out I could almost cry after listening to your wonderful voice explaining the way forward in such perfectly simple terms. Thank you!
Another OMG from me! I thought I knew the trim function and nearly skipped this video. Not only i learnt so much more but also learnt not to skip your videos!!!
Here is a VBA Function I put together to use as a TRIMALL() in spreadsheets. It has the added umph of allowing a range to be passed in to generate a sentence of values from a range with an optional delimiter parameter for a delimited string. 'FUNCTION TRIMALL 'PARAMS: ' text ' delim (optional for ranges, defaults to single-space) 'RETURNS: ' If the value in the 'text' parameter is a string, the value ' with non-breaking spaces (chr(160)) replaced by standard spaces (chr(32)) ' double-spaces removed and trimmed of leading and trainling spaces. ' ' Additionally, if the value in 'text' is a range, ' a string of values with chr(160) replaced by chr(32) and each cell in the range ' trimmed of double-spaces with leading and trailing spaces removed and ' separated by a space or optionally-provided delimiter ('delim'). ' Function TRIMALL(text As Variant, Optional delim As String = " ") Dim s As String 'result string of text Dim t As String 'temp string 'if the variant of text is a Range If (VarType(text) = 8204) Then Dim r As Range Dim c As Range Set r = text For Each c In r 'replace non-breaking spaces t = Trim(Replace(c.Value, Chr(160), " ")) 'since it is a range, add a delimiter between each trimmed value If s = Empty Then s = t Else s = s & delim & t Next c Else 'replace non-breaking spaces and trim the text value s = Trim(Replace(text, Chr(160), " ")) End If 'check for instances of double-spacing and trim those out While InStr(s, " ") > 0 s = Replace(s, " ", " ") Wend 'finally, trim the result and return it TRIMALL = Trim(s) End Function
The way you alwaaaaayyyyyyyssss save my life. Only GOD can pay you back. I was trying to fix that for months. I was reduce to find a VBA code until you come up with the LIGHTS. The only Excel Angel on earth :)
Thank you for this video! so easy to understand and very well explained.
this was a super help for me i used this today & fixed a lot of issues with spaces on my spreadsheet. thanks so much for your time and detication for these tutorials they are a really help for a lot of people worldwide !!!! awesome work !!!
Straight to the point. I've been searching this method for an hour. Thanks for the tricks!
That's helped clear up some annoyances I've had before copying data from / referring to another spreadsheet. Many thanks.
Leila, you rock! I've tried all the quick and easy ways of solving this issue and all failed. Finding the right character did the trick. Thanks a ton! Subscribed!
I wish you'd made this video last week because I encountered exactly this problem on Sunday whilst reconciling expenses. Based on topic expertise, delivery, A/V quality, visuals, and narration, you are the best teacher on YouTube bar none. Furthermore, your lessons and courses yield excellent, professional results. I look forward to your Thursday morning installments and have watched your accounting videos since they began a couple of months ago. Great perspective. Thanks many times over.
Bundles of video out there on youtube, but this one covers the true problem statement and solution. Appreciated. Keep sharing such videos.
You are my hero! Leila G. I asked Google how to get rid of spaces in Excel and you showed up and solved the problem.
You are the best .. i always found a solution in your videos..thank you and god bless you.
Thanks Leila, you have always understand the problems before I encounter them.
This video just saved my tons of time. Love you so much ❤️
@LeilaGharani